Thread: Formula in 2 cells (2002/XP)

1. 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!

2. 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. Right-click 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

thank you

4. 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).

5. Re: Formula in 2 cells (2002/XP)

What kind of info would you like?

6. Re: Formula in 2 cells (2002/XP)

Not sure what to do after we copy the code in the view code area.

7. 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.

8. 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

9. 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>

10. Re: Formula in 2 cells (2002/XP)

That does exactly what I needed. Thanks a lot!

11. 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.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•