Results 1 to 11 of 11
Thread: Formula in 2 cells (2002/XP)

20050203, 18:29 #1
 Join Date
 Jan 2001
 Posts
 313
 Thanks
 0
 Thanked 0 Times in 0 Posts
Formula in 2 cells (2002/XP)
I have a user who needs a formula similar to this. If in A1 a number is less than 2, then put 2, and the remainder (if greater than 2) goes in cell A2. So if the number is 7, then 2 goes in A1 and 5 goes in A2, if the number is 1, then 1 in A1 and 1 in A2, etc.....
How do we accomplish this? THANKS!

20050203, 18:41 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Formula in 2 cells (2002/XP)
You can't use a formula for the first part, for the user should be able to enter a value in A1. You need the Worksheet_Change event for this. Rightclick the sheet tab and select View Code from the popup menu. Enter the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
Dim varValue As Variant
Application.EnableEvents = False
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
For Each oCell In Intersect(Target, Range("A1:A10"))
varValue = oCell.Value
If IsNumeric(varValue) Then
If varValue > 2 Then
oCell.Value = 2
oCell.Offset(0, 1) = varValue  2
Else
oCell.Offset(0, 1).Clear
End If
End If
Next oCell
End If
Application.EnableEvents = True
Set oCell = Nothing
End Sub

20050203, 19:03 #3
 Join Date
 Jan 2001
 Posts
 313
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula in 2 cells (2002/XP)
thank you

20050203, 19:28 #4
 Join Date
 Jan 2001
 Posts
 313
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula in 2 cells (2002/XP)
Hans, thank you for this information, however, we are both lost (does not take much as we are not expert excel users).
Can you provide any more info?

20050203, 19:31 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Formula in 2 cells (2002/XP)
What kind of info would you like?

20050203, 19:37 #6
 Join Date
 Jan 2001
 Posts
 313
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula in 2 cells (2002/XP)
Not sure what to do after we copy the code in the view code area.

20050203, 19:48 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Formula in 2 cells (2002/XP)
Switch back to Excel and enter something in A1 (or A2, or A3,...) to test if it works.
In the code as posted, the range A1:A10 is monitored. If the user enters a number higher than 2, 2 is put in the cell, and the remainder in the cell next to it. Otherwise, whatever the user entered is put into the cell, and the cell next to it is cleared. If you want to monitor another range, change the code accordingly.
I have attached a demo workbook. See what happens if you enter a numeric or text value in one of the cells A1:A10.

20050204, 13:59 #8
 Join Date
 Feb 2005
 Posts
 2
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula in 2 cells (2002/XP)
Hans,
To take this a little further. I would like to enter numbers in the A column until I reach a value, say 2. Once that is reached, I would like to put the remainder in the B column and then all the rest of the values in the B column.
If I enter a 1 in A1, it puts a 1 in. Then I enter a 2 in A2. Since the sum would be over 2, it would put a 1 in A1 and the remainder in B2. Next, if I entered anything in A3, since I have already reached the max amount I can enter in the A column, the whole value would be put in B3. And so on. I hope this makes sense.
Thanks

20050204, 14:20 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Formula in 2 cells (2002/XP)
Does this modification to Hans' code do what you want?
Steve
<pre>Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
Dim varValue As Variant
Dim varTotal As Variant
Dim varPrevTotal As Variant
Application.EnableEvents = False
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
For Each oCell In Intersect(Target, Range("A1:A10"))
varTotal = Application.WorksheetFunction.Sum(Range(Range("a1" ), oCell))
varPrevTotal = 0
If oCell.Address <> "$A$1" Then _
varPrevTotal = Application.WorksheetFunction. _
Sum(Range(Range("a1"), oCell.Offset(1, 0)))
varValue = oCell.Value
If IsNumeric(varValue) Then
If varTotal >= 2 Then
oCell.Value = 2  varPrevTotal
oCell.Offset(0, 1) = varValue  oCell
If oCell.Value = 0 Then oCell.ClearContents
If oCell.Offset(0, 1).Value = 0 Then _
oCell.Offset(0, 1).ClearContents
Else
oCell.Offset(0, 1).Clear
End If
End If
Next oCell
End If
Application.EnableEvents = True
Set oCell = Nothing
End Sub</pre>

20050204, 14:24 #10
 Join Date
 Feb 2005
 Posts
 2
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula in 2 cells (2002/XP)
That does exactly what I needed. Thanks a lot!

20050204, 14:25 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Formula in 2 cells (2002/XP)
Welcome to Woody's Lounge!
Steve has already posted modified code. If it is acceptable that the input remains unchanged, and the calculated values are in other columns, you could use formulas. See attached workbook.