macro (change event) to increment by one row

JKCC
JKCC used Ask the Experts™
on
Hi Guys,

Wondering if you can help again? I have a macro taken from this site, but need some tweeking to fit my scenario.

All I want is the formula in column H4 to be copied down one row at a time as data is entered in column A4 and down. I think I need a worksheet change event.

the current formula I have is as follows but I don't require all the additional vlookup stuff:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A4:A" & Rows.Count)) Is Nothing Then
        If Target <> "" Then
          Range("H" & Target.Row).Formula = "=VLOOKUP(LEFT(H" & Target.Row & ",17),'X:\APS 3 MRAP\Processing\Tools\[Tags.xlsx]Sheet1'!$A$2:$H$10000,2,FALSE),"""")"
        Else
          Range("C" & Target.Row) = ""
        End If
    End If
End Sub


Any help would be greatly appreciated.

Regards,
J
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Change

          Range("H" & Target.Row).Formula = "=VLOOKUP(LEFT(H" & Target.Row & ",17),'X:\APS 3 MRAP\Processing\Tools\[Tags.xlsx]Sheet1'!$A$2:$H$10000,2,FALSE),"""")"

to

          Range("H4").copy Range("H" & Target.Row)
and

          Range("C" & Target.Row) = ""

to

          Range("H" & Target.Row) = ""

Author

Commented:
that works perfectly, thanks! Is there a quick way to add in another column? I am just putting this together and need column I to work in the same way as column H?

Or should I ask another questions since I didn't specify this originally. sorry, I've been off work for a year and not familiar (again) with all this stuff.

Regards,
J
Copy the newly added rows below themselves and change the H's to I's

Author

Commented:
Your ace! Thankyou so much...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial