Apply a vba to multiple rows

[DCEXC]

Hi, im literally brand new to VBA's

i have a worksheet that is using a vlookup formula, and its basically allows me to over write a cell but then restore the formula if i delete the overwrite.

i need to apply the same macro to all the rows and continue the cell references

macro:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "O7" Then
If IsEmpty(.Value) Then
Application.EnableEvents = False
.Formula = "=T7"
Application.EnableEvents = True
End If
End If
End With
End Sub

i need this to continue down to O32 and for the "T" row numbers to follow i.e (o8 = t8 etc). pretty much as if i dragged the formula down.

Could be simple, but i have only found very broad info and cant make sense of it.

 

[James006]

Hi,

The macro you posted is an Event macro ... not a standard one ...

Could you briefly explain your process ...

 

[Fluff]

Hi & welcome to MrExcel.
How about

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Not Intersect(Target, Range("O7:O32")) Is Nothing Then
        If IsEmpty(Target.Value) Then
            Application.EnableEvents = False
            Target.FormulaR1C1 = "=rc20"
            Application.EnableEvents = True
        End If
    End If
End Sub

 

[DCEXC]

That is brilliant, thankyou!

but where is the reference to the T column? so if i decide to move the t column do a different one for a different sheet, which part of the code do i change?

 

[Michael M]

Right here...

Target.FormulaR1C1 = "=rc20"

In the statement c20..."c" =column and 20 = "T"

 

[DCEXC]

Right here...

Target.FormulaR1C1 = "=rc20"

In the statement c20..."c" =column and 20 = "T"

you guys are the best

 

[Michael M]

Glad it helped...

 

[DCEXC]

Found one issue, when i press delete on the cell if i dont want to use my custom input, it doesnt auto bring back the formula like it used to, it just shows =rc20

 

[DCEXC]

all good i fixed it, however, how do i make it start from row 24 onwards?

 

[Michael M]

Try using

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Not Intersect(Target, Range("O[color=red]24[/color]:O32")) Is Nothing Then
        If IsEmpty(Target.Value) Then
            Application.EnableEvents = False
            Target.FormulaR1C1 = "=rc20"
            Application.EnableEvents = True
        End If
    End If
End Sub