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