Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Applying formula to variable number of rows | Excel Worksheet Functions | |||
Insert Variable Number of Rows; With Loop | Excel Worksheet Functions | |||
Insert Next? Or insert a variable number of records...how? | Excel Discussion (Misc queries) | |||
insert variable number of rows | Excel Programming | |||
Insert rows in depending on criteria in variable columns | Excel Programming |