Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    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!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 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. 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

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula in 2 cells (2002/XP)

    thank you

  4. #4
    3 Star Lounger
    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?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Formula in 2 cells (2002/XP)

    What kind of info would you like?

  6. #6
    3 Star Lounger
    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.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 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.

  8. #8
    New Lounger
    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

  9. #9
    WS Lounge VIP sdckapr's Avatar
    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>


  10. #10
    New Lounger
    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!

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 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.

Posting Permissions

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