VBA vlookup with Variable for lookup value

[Gus68]

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

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

 

[steve the fish]

Try:

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

 

[Gus68]

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]

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]

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]

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.