Excel VB-Copy formula down until adjacent cell (left) is blank?

[Guest]

Here is exactly what I am trying to do through VB in Excel:

Weekly data pull fills colums A:G. Row count is always different. I am
modifying the data pull through VB, and I have a VLOOKUP formula in cell H2.
What I want VB to do is copy that formula down column H to the last row (with
data) each week. I guess I want it to be dynamic so that as rows
decrease/increase the formula is only copied down to the final row/record.

I know someone out of this smart group will know how to do this!

Thanks in advance!

Tony

 

[Guest]

(pseudo code)

in a macro
....

dim lngLastrow as long
dim rngTargetStart as range
dim rngTargetEnd as range


' finds the LAST row of data to go to
lnglastrow = worksheet(x).range("A65536").end(xlup).cells(2,1).row

set rngTargetstart = worksheet(x).range("H2") ' for my example, the
formula is stored in H1
set rngTargetEnd = worksheet(x).cells(lngLastRow,8) ' last row, column H

worksheet(x).range("H1").copy range(rngTargetStart,rngTargetEnd)

' clean up object variables.