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
number on top of numbers
- Thread starter wildgoose
- Start date
- Joined
- Mar 21, 2011
- Messages
- 1,960
- Reaction score
- 0
- Points
- 36
- Excel Version(s)
- O365
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?
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
3
3
3
3
3
3
2
2
2
2
1
1
Last edited:
- Joined
- Mar 21, 2011
- Messages
- 1,960
- Reaction score
- 0
- Points
- 36
- Excel Version(s)
- O365
Uum, that is different to what I expected 
Try this
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