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

Open in new window


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!
LVL 13
Shanan212Asked:
Who is Participating?
 
andrewssd3Connect With a Mentor Commented:
Sorry my last post was not quite right - if I understand your intention correctly, this is simpler:

    Dim n As Long
    Dim lastrow As Long
    Dim baseWks As Excel.Worksheet
    
    Dim rngTarget As Excel.Range
    
    Set baseWks = ActiveSheet
    lastrow = 25
    
    Set rngTarget = Range(baseWks.Cells(1), baseWks.Cells(lastrow, 1))
    
    rngTarget.FormulaR1C1 = "= VLookup(RC[2], 'GL Summary'!Ranges, 2, False)"

Open in new window


Just filling in a random number for your lastrow variable. You can fill all the cells in one statement using the R1C1 reference style.
0
 
kgerbChief EngineerCommented:
I think you probably need to concatenate the value instead of including the Range(n, "C") in the string

Untested:
For n = BaseWks.UsedRange.Row To lastrow
        BaseWks.Cells(n, "A").Formula = "= VLookup(" & Range(n, "C").Value & ", 'GL Summary'!Ranges, 2, False))"
Next n

Open in new window


Kyle
0
 
kgerbChief EngineerCommented:
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

Open in new window

0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
Shanan212Author Commented:
    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

Open in new window


I am getting application defined/object defined error on the line inside for-loop

Any idea? :o

Thanks for the help so far!
0
 
dlmilleCommented:
You might get that error if you don't prefix what worksheet you're working with.

You might need BaseWks.Cells(N+1,"C").value 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
0
 
andrewssd3Commented:
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:

 BaseWks.Cells(n, "A").Formula = "= VLookup(Range(n, ""C"").Value, 'GL Summary'!Ranges, 2, False)"

Open in new window


However, there is a simpler way to do what you want to do - I'll post in a moment
0
 
dlmilleCommented:
I don't think so Kgerb has it right - the Range object needs to be outside the greater quotes and concatenated in...
0
 
kgerbConnect With a Mentor Chief EngineerCommented:
This one works.  you had an extra ")".

This is if you are looking up text:
For n = BaseWks.UsedRange.Row To lastrow
    BaseWks.Range("A" & n).Formula = "= VLookup(""" & Cells(n + 1, "C").Value & """, 'GL Summary'!Ranges, 2, False)"
Next n

Open in new window


This is if you are looking up numbers:
For n = BaseWks.UsedRange.Row To lastrow
    BaseWks.Range("A" & n).Formula = "= VLookup(" & Cells(n + 1, "C").Value & ", 'GL Summary'!Ranges, 2, False)"
Next n

Open in new window


Kyle
0
 
Shanan212Author Commented:
Both solutions worked!

Thanks!
0
All Courses

From novice to tech pro — start learning today.