Andrew_Rossington
Board Regular
- Joined
- Feb 12, 2007
- Messages
- 121
A while ago I asked for some help with creating code that would add a formula in a certain cell if the adjacent cell had a value in it.
I was very kindly offered the following solution:
This works perfectly.
What I'm currently trying to achieve is to use the above code to add a formula whose cell reference changes depending on which row it's on.
For example, if there was a value in B2, this code could be used to enter"=COUNTIF(Classes!C:C,B2)" in cell A2. However, for further values in column B, the respective column A cell would still display the formula as entered. Is it possible to have the formula more along the lines of "=COUNTIF(Classes!C:C,B#)", whereby the reference in the formula represents the current row?
I was very kindly offered the following solution:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Ce As Range, LstRw As Long
Application.ScreenUpdating = False
LstRw = Cells(Rows.Count, "B").End(xlUp).Row
LstRw2 = Cells(Rows.Count, "A").End(xlUp).Row
If LstRw < LstRw2 Then LstRw = LstRw2
If LstRw < 4 Then Exit Sub
For Each Ce In Range("B2:B" & LstRw)
If Len(Ce.Value) = 0 Then
Range("A" & Ce.Row).Value = vbNullString
Else
Range("A" & Ce.Row).Formula = "=Row() - 1"
End If
Next Ce
Application.ScreenUpdating = True
End Sub
This works perfectly.
What I'm currently trying to achieve is to use the above code to add a formula whose cell reference changes depending on which row it's on.
For example, if there was a value in B2, this code could be used to enter"=COUNTIF(Classes!C:C,B2)" in cell A2. However, for further values in column B, the respective column A cell would still display the formula as entered. Is it possible to have the formula more along the lines of "=COUNTIF(Classes!C:C,B#)", whereby the reference in the formula represents the current row?