Results 1 to 5 of 5

Thread: number on top of numbers

  1. #1

    number on top of numbers



    Register for a FREE account, and/
    or Log in to avoid these ads!

    What i want to do is take data from columns "AA" and "BB" and have them inserted into the columns and rows to the left. The 1st event entered is AA=5 BB=10. AA determines which column and BB determines how many rows to fill. The 2nd event AA=6 BB=12 go to column "E" which is 6 and fill down to the number 12. Now when another event happens to have the same column AA=5 BB=15 column D which is 5 and add a 1 to rows 0-15. There may be as many as 50 events to calculate. If an event happens in the sane column several times it would be added to the data in those cells. I hope this explains. thanks in advance. Mark
    Attached Files Attached Files

  2. #2
    What happens if there is an event such as A=5, B=12, that is less to fill than there was in a previous event?

  3. #3
    Bob, Lets say the events in column "AA" were all 5 and there were 3 events. AA=5 BB=10,AA=5 BB=12, AA=5 BB=6 the rows would be filled like this. All 3 events went to 6, 2 events down to 10 and 1 event down to 12. Thanks for your time hope this helps. mark

    3
    3
    3
    3
    3
    3
    2
    2
    2
    2
    1
    1
    Last edited by wildgoose; 2012-11-15 at 12:45 PM.

  4. #4
    Uum, that is different to what I expected

    Try this

    Code:
    
    Private prevValue As Variant
    
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    
        On Error GoTo ws_exit
    
    
        Application.EnableEvents = False
    
    
        With Target
    
    
            If .Row > 2 Then
            
                If .Column = 21 Then
    
    
                    If prevValue = "" Then
                    
                        If .Offset(0, 1).Value <> "" Then
                    
                            Call PopulateValues(.Value, .Offset(0, 1).Value)
                        End If
                    End If
                ElseIf .Column = 22 Then
    
    
                    If prevValue = "" Then
    
    
                        If .Offset(0, -1).Value <> "" Then
                    
                            Call PopulateValues(.Offset(0, -1).Value, .Value)
                        End If
                    End If
                End If
            End If
        End With
    
    
    ws_exit:
        Application.EnableEvents = True
        Application.CutCopyMode = False
    End Sub
    
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        prevValue = Target.Value
    End Sub
    
    
    Private Function PopulateValues(AA, BB)
    Dim col As Long
    Dim lastrow As Long
    Dim i As Long
    
    
        With Me
        
            col = Application.Match(AA, .Rows(1), 0)
            lastrow = .Cells(.Rows.Count, col).End(xlUp).Row
            
                If lastrow > 1 Then
                
                    .Range("AA1").Value = 1
                    .Range("AA1").Copy
                    .Cells(2, col).Resize(BB).PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
                    .Range("AA1").Value = ""
                End If
                .Cells(lastrow + 1, col).Resize(BB - lastrow + 1).Value = 1
        End With
    End Function

  5. #5
    Thanks Bob i will give it a try. Looks complicated but I will do my best to figure it out. I will let you know, thanks for the time I really appreciate it. Mark

Posting Permissions

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