VBA vlookup with Variable for lookup value

Gus68

New Member
Joined
Feb 13, 2011
Messages
11
Hi all,

Firstly apologies if this has been asked elsewhere but I've spent the better part of the afternoon searching and can't find an answer to my exact problem.

I have written some code to look at an open workbook, get it's name and then open the last saved version of the same workbook. I'm then adding a vlookup to the new workbook using an input from the user to get the cell reference for the lookup value.

Everything is working to the point that it is correctly opening the older wb and inserting the vlookup but for some reason the lookup value is returning the result with the ' ' in the formula and this is obviously causing it to error. I can simply edit the formula and remove the ' ' but I'm so close with getting <acronym title="vBulletin" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(87, 65, 35);">vb</acronym> to automate it and I'd hate to be stopped by something so small.

My code:-

Code:
Sub Lookup_To_Previous()
'
'Define the open Workbooks
    Dim wb As Workbook, x As String
    For Each wb In Workbooks
    If wb.Name <> ThisWorkbook.Name Then x = wb.Name
    Next wb
    Workbooks(x).Activate
    
'Gets the name of the current workbook and then tries to find the last saved version
    WBName = ActiveWorkbook.Name
    SearchString = Left(WBName, 5)
    
    Workbooks.Open Filename:="G:\Systems\Reporting\SAP Pre-Payrun\Saved Reports\" & SearchString & "*"
    
'Gets the name of the newly opened Workbook for the lookup
    Dim WBLookup
    WBLookup = ActiveWorkbook.Name
    Workbooks(WBName).Activate
    
    Range("A5").Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(1, 1).Range("A1").Select
    
'Adds the lookup formula - will prompt for the Employee No. Column
    Dim EmpCol
    Dim Default
    
    Default = "Must Enter Cell Reference - i.e. A6."
    EmpCol = InputBox("Employee # Location", Default)
    
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(" & EmpCol & ",'" & "[" & WBLookup & "]" & "ALL'!R5C1:R25C1000,5,FALSE)"
       
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=RC[-11]-RC[-1]"
    
End Sub

This is the formula that vb is returning:-
=VLOOKUP('A6','[PV07A PAYROLL REC AM 030715.xlsx]ALL'!$A$5:$ALL$25,5,FALSE)

The formula is 100% correct but the ' ' around the A6 are making a mess of it.

I would be most appreciative if anyone can tell me how to fix it - I'm sure I've just been looking at it way too long now and can't see the forest for the trees!

Thanks in advance!

Tracey
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,293
Office Version
  1. 365
Platform
  1. Windows
Try:

Code:
ActiveCell.FormulaR1C1 = "=VLOOKUP(""" & Range(empCol) & """,R5C1:R25C1000,5,FALSE)"
 

Gus68

New Member
Joined
Feb 13, 2011
Messages
11
Hi Steve,

Thanks for the reply but that's doing the same thing - only this time with the " " around the A6 reference. :(
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,293
Office Version
  1. 365
Platform
  1. Windows
It should be putting the contents of the cell reference you typed into the inputbox inside the VLOOKUP formula. Is it text or numbers inside the cell? If numbers try:

ActiveCell.FormulaR1C1 = "=VLOOKUP(" & Range(empCol) & ",R5C1:R25C1000,5,FALSE)"

Column 1000 is a bit of overkill by the way so you could use:

ActiveCell.FormulaR1C1 = "=VLOOKUP(" & Range(empCol) & ",R5C1:R25C5,5,FALSE)"
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,293
Office Version
  1. 365
Platform
  1. Windows
A couple of other ways. These put the cell reference in rather than the cell value:

ActiveCell.FormulaR1C1 = "=VLOOKUP(" & Range(empCol).Address(ReferenceStyle:=xlR1C1) & ",R5C1:R25C5,5,FALSE)"
ActiveCell.Formula = "=VLOOKUP(" & empCol & ",A5:E25,5,FALSE)"
 

Gus68

New Member
Joined
Feb 13, 2011
Messages
11
Apologies for not replying earlier!Thanks for the other options - they are very close but I need it to return the relative reference as the end user will need to fill the formula down multiple rows. The input from the User is the cell address where the Employee number resides - this will always be an RC reference e.g. A6, B10 etc. I ended up just writing some extra code to find the " and remove them - not elegant I know, but I had run out of patience with it.Thanks for your help - much appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,337
Messages
5,601,026
Members
414,422
Latest member
acegreen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top