Solved

Vlookup and table arrays in Excel VBA

Posted on 2011-09-30
9
423 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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 250 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 250 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvieā€¦
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

808 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