Solved

Vlookup and table arrays in Excel VBA

Posted on 2011-09-30
9
412 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
 
LVL 41

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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 41

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

911 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now