Solved

Vlookup and table arrays in Excel VBA

Posted on 2011-09-30
9
469 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

728 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