Shanan212
asked on
Vlookup and table arrays in Excel VBA
For n = BaseWks.UsedRange.Row To lastrow
BaseWks.Cells(n, "A").Formula = "= VLookup(Range(n, "C").Value, 'GL Summary'!Ranges, 2, False))"
Next n
Hi,
I am trying to run the above formula and its giving me syntax error at (n, "C") point!
I am trying to lookup a value in n row C column
Thanks!
Any help is much appreciated!
and you'll probably want to use cells instead of range. Should have caught that in the first post.
For n = BaseWks.UsedRange.Row To lastrow
BaseWks.Cells(n, "A").Formula = "= VLookup(" & Cells(n, "C").Value & ", 'GL Summary'!Ranges, 2, False))"
Next n
ASKER
Set BaseWks = wkb.Sheets("Summary Data")
Range("A1").Activate
ActiveCell.EntireColumn.Insert
lastrow = BaseWks.UsedRange.Rows.Count - BaseWks.UsedRange.Row + 1
n = 1
For n = BaseWks.UsedRange.Row To lastrow
BaseWks.Cells(n, "A").Formula = "= VLookup(" & Cells(n + 1, "C").Value & ", 'GL Summary'!Ranges, 2, False))"
Next n
I am getting application defined/object defined error on the line inside for-loop
Any idea? :o
Thanks for the help so far!
You might get that error if you don't prefix what worksheet you're working with.
You might need BaseWks.Cells(N+1,"C").val ue in your vlookup. Not sure you're referencing active sheet or not.
FYI only Also, using usedrange can be dangerous. UsedRange does not always start at row 1 and can include more columns or rows than the actual data represents...
Dave
You might need BaseWks.Cells(N+1,"C").val
FYI only Also, using usedrange can be dangerous. UsedRange does not always start at row 1 and can include more columns or rows than the actual data represents...
Dave
The most immediate problem is that you need to double up the double quotes inside your formula string, and you have an extra closing bracket:
However, there is a simpler way to do what you want to do - I'll post in a moment
BaseWks.Cells(n, "A").Formula = "= VLookup(Range(n, ""C"").Value, 'GL Summary'!Ranges, 2, False)"
However, there is a simpler way to do what you want to do - I'll post in a moment
I don't think so Kgerb has it right - the Range object needs to be outside the greater quotes and concatenated in...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Both solutions worked!
Thanks!
Thanks!
Untested:
Open in new window
Kyle