Event Procedure

[Paul Johnson]

3 4 7 7 8 8 8 8 8 8 Band A A A B C C C E 0
3 1 3 0 1 0 0 0 0 0 Offset 1 1 1 2 3 3 3 5 ###
Distribution
A
B C D E F G H I J 13 7 7 7 20 8 7 9
A B C D E F G H I J
1 1 1 1 1 1 1 1 1 1 13 7 7 7 20 8 7 9

Hi,
Im trying with little sucess to write an procedure, that when a value "1" is
entered in the cell below the blue A, a match is performed with the "A" in
blue against the white A A A B CC....Row, for each match a formula must be
entered , in the corresponding row the "1" was entered. The same procedure
will run for B C D etc when a 1 is entered.

Many Thanks

Paul

 

[Tom Ogilvy]

This assumes the A A A B C C C E 0
is in row 1 starting in F1

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cell As Range
Dim rw As Range
If Target.Count > 1 Then Exit Sub
If Target.Value = 1 Then
Application.EnableEvents = False
On Error GoTo ErrHandler
sTarget = Target.Offset(-1, 0).Value
rw = Target.Row
Set rng = Range("F1:N1")
For Each cell In rng
If cell.Value = sTarget Then
Cells(rw, cell.Column).Formula = "=Trunc(rand()*100+1)"
End If
Next
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Right click on the sheet Tab, select view code and paste in the code.
Replace my formula with whatever formula you want.