Need to insert formula to a variable number of rows
 

I am trying to copy a vlookup formula, using vba, down colum "U" to
rows that vary in length every time a do this report (newCount is the
variable amount of Rows). The main problem with my code is the first
argument in the vlookup formula. It has to be in relative mode down
colum "E" starting in row 2; hence x=count +1, X being the row in colum
"E". Can some one help. Here is my code.

Sub fillRange()
Range("u2").Select
Dim Count As Integer
Dim newCount As Integer
Dim fCount As Integer
newCount = 215
For Count = 1 To newCount
x = Count + 1
ActiveCell.Offset(Count - 1, 0) = "=VLookup($e&(x),'Program
Lookup' _ !A2:L500, 5, False)"
Next Count
End Sub

Thanks

Need to insert formula to a variable number of rows
 

I assume E2 needs to be relative and A2:L500 should be absolute

Sub fillRange()
Dim newCount as Long
newCount = 250
Range("U2").Resize(newCount,1).Formula = _
"=VLookup(E2,'Program Lookup'" _
& "!$A$2:$L$500, 5, False)"

End Sub

--
Regards,
Tom Ogilvy


"edoc abv" wrote in message
oups.com...
I am trying to copy a vlookup formula, using vba, down colum "U" to
rows that vary in length every time a do this report (newCount is the
variable amount of Rows). The main problem with my code is the first
argument in the vlookup formula. It has to be in relative mode down
colum "E" starting in row 2; hence x=count +1, X being the row in colum
"E". Can some one help. Here is my code.

Sub fillRange()
Range("u2").Select
Dim Count As Integer
Dim newCount As Integer
Dim fCount As Integer
newCount = 215
For Count = 1 To newCount
x = Count + 1
ActiveCell.Offset(Count - 1, 0) = "=VLookup($e&(x),'Program
Lookup' _ !A2:L500, 5, False)"
Next Count
End Sub

Thanks