VLOOKUP in Macro

[msquared99]

I am working on some code where I identify a workbook as "wbo" by way of GetOpenFileName.

Set wba = Workbooks.Open(fName1)

I am trying to do a VLOOKUP like this:

Range("O2:O15").FormulaR1C1 = "=VLOOKUP(RC[-1],wbo R2C15:R150C15, 1, 0)"

How do I reference wbo in the formula?

Thanks.

 

[msquared99]

One other thing, there is only 1 worksheet in the workbook wbo, but its name varies.

 

[Monty]

Hello msquared99

Can you please upload the files to help you...Monty

 

[msquared99]

OK, wbo = SalesLimit1-01262017-AM and wba = SalesLimit2-01272017-PM

The macro is allowing the user to choose the spreadsheets. On the first selection you choose SalesLimit1-01262017-AM, on the second selection you choose SalesLimit2-01272017-PM.

The macro is adding a 1 to all rows with data in column M, creating a unique ID in column N, and doing a VLOOKUP in column O. The LOOKUP is comparing what is in wbo to what is in wba.

Note: I have not setup LastRow yet. I'll do that once I get the VLOOKUP working.

Sub GetFile()

Dim fName1 As Variant, wbo As Workbook
Dim fName2 As Variant, wba As Workbook

fName1 = Application.GetOpenFilename(filefilter:="Excel Files (*.XLS), *.XLS", Title:="Select the prior spreadsheet:")

  If fName1 = False Then Exit Sub

  Set wbo = Workbooks.Open(fName1)
  
fName2 = Application.GetOpenFilename(filefilter:="Excel Files (*.XLS), *.XLS", Title:="Select the current spreadsheet:")

  If fName2 = False Then Exit Sub

  Set wba = Workbooks.Open(fName2)
  
  wba.Activate
  
  Range("M2:M5").FormulaR1C1 = "1"
  Range("N2:N5").FormulaR1C1 = "=RC[-13]&RC[-10]"
  Range("O2:O5").FormulaR1C1 = "=VLOOKUP(RC[-1],'[" & wbo.Name & "'!&R2C15:R150C15,1,0)"
  
  
  wbo.Close savechanges:=False 'or True
  wba.Close savechanges:=True 'or False

End Sub

 

[Monty]

Hello msquared99

Still trying to understand your question as per the macro is concerned leave it to me...can you do a simple vlookup in excel to understand what range your taking and from which book.

Thank you...

 

[msquared99]

Here you go:

=VLOOKUP(N2,'[SalesLimit1-01262017-AM.xls]SalesData-012620'!$N$2:$N$7,1,0)

Where wbo would replace SalesLimit1-01262017-AM.xls]SalesData-012620

 

[Monty]

Hello msquared99

Thanks for providing Vlookup.

Here is the macro file as per your requirement..I assume you wanted output to be in PM workbook from AM.

Attached is macro file..Click on the button..When asking for a file first select AM and Then PM.

Hope you like this..

Please let me any challenges.

 

[Arpanakumar]

Hello Monty..

Iam keep checking for question and answer on this forum and this solution will also helps me in my project...Thank you again.

 

[Monty]

Hello Arpana.

You are most welcome.

 

[msquared99]

Monty:

Thanks! But...

The issue is that both the workbook and worksheet names will change with fName1.

This line of code: Range("O2").FormulaR1C1 = "=VLOOKUP(RC[-1],'[SalesLimit1-01262017-AM.xls]SalesData-012620'!R2C14:R7C14,1,0)"

Needs to have this part "SalesLimit1-01262017-AM.xls]SalesData-012620" set as a variable to read like: Range("O2").FormulaR1C1 = "=VLOOKUP(RC[-1],'" & fName1 & WBName & "'!R2C14:R7C14,1,0)"

Where fName1 replaces SalesLimit1-01262017-AM.xls and WBName replaces SalesData-012620 (the variable tab name)

So how do I setup WBName?

 

[msquared99]

So, I tried this and get a Run-Time Error 438

Range("O2").FormulaR1C1 = "=VLOOKUP(RC[-1],'[" & fName1 & "]" & WBName & "'!R2C14:R7C14,1,0)"

It is like something in this part of the code is not correct:

Set wbo = Workbooks.Open(fName1)
  wbo.Activate 'New code
  Set WBName = wbo.ActiveSheet 'Worksheets(1) 'New code
  'Set WBName = wbo.Worksheets(SheetName) 'New code

 

[msquared99]

So, I'm working on this code and still get a Run-Time Error 438 on the VLOOKUP.

Anybody?

 

[msquared99]

I figured it out. Since the workbook only has one worksheet in it, I do not need to assign the worksheet a name.

This code worked:

Range("O2").FormulaR1C1 = "=VLOOKUP(RC[-1],'[" & fName1 & "]'!R2C14:R7C14,1,0)"