Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Vlookup and table arrays in Excel VBA

Posted on 2011-09-30
9
Medium Priority
?
568 Views
Last Modified: 2012-05-12
    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!
0
Comment
Question by:Shanan212
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 12

Expert Comment

by:kgerb
ID: 36893969
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
 
LVL 12

Expert Comment

by:kgerb
ID: 36893976
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
 
LVL 13

Author Comment

by:Shanan212
ID: 36894112
    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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 42

Expert Comment

by:dlmille
ID: 36894171
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
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36894189
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
 
LVL 42

Expert Comment

by:dlmille
ID: 36894197
I don't think so Kgerb has it right - the Range object needs to be outside the greater quotes and concatenated in...
0
 
LVL 12

Assisted Solution

by:kgerb
kgerb earned 1000 total points
ID: 36894218
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
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 1000 total points
ID: 36894260
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
 
LVL 13

Author Closing Comment

by:Shanan212
ID: 36894297
Both solutions worked!

Thanks!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question