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

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

(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.








"Tony P." wrote:

> 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
>
>