VBA Button to select source sheet for Vlookup

[calmdrae]

Hi Folks,

I am reasonably new to VBA and I have been trying to get this working for a couple weeks now and after numerous google seaches, I am almost defeated.

I am working on a tool used for quoting my customers, I have created it so that once you enter a part number it pulls the current item cost and stock level from a speperate workbook using VLookup you can then either use this cost manually or via a preset table to calculate the sell price you will use.
This all works great and does exactly what I want it to. The problem that arises is that every month our accounting software produces a new workbook that contains that months costs and stock levels. Currently I have to go through and manually edit the Vlookup formulas to pull from new months workbook. Which is a pain but not the end of the world (there are approx 20 cells that use Vlookup), I now however have a second preson that is quoting and not particullary excel literate.
I would like to create a button on the quote sheet that when pressed opens a selection window for the user to select the new months workbook on their PC and uses that info to update the Vlookup formulas to pull from that workbook and saves the quote sheet so that the button only needs to be used when the new months work book is issued.

So far I have been able to create the button that goes through the user selection process then stores the file location (full path) in a cell within the worksheet and saves the sheet.
What I have been hoplessly unable to do is either use that location cell to edit the Vlookup formula or use VBA to manipulate the Vlookup formula based on the users selected file location.

The current two functional VLookup formulas are:
=IFERROR(VLOOKUP(B27,'C:\Users\Desktop\WIP\[Stock valuation May 2020.xlsx]SVR'!$A$11:$K$755,6,FALSE),"") for cost
=IFERROR(VLOOKUP($B25,'C:\Users\Desktop\WIP\[Stock valuation May 2020.xlsx]SVR'!$1:$1048576,5,FALSE),"") for stock level

The user input button currently populates cell P11 with the full file path EG C:\Users\Desktop\WIP\Stock valuation May 2020.xlsx

I have tried various versions of manully tring to insert P11 into the formual EG =IFERROR(VLOOKUP($B25,'[P11]SVR'!$1:$1048576,5,FALSE),"")
But I cannot find a way that will function.

The cell ranges within the new months worksheet and the quote tool never change, so as far as I can tell it is only the loctaion on the PC that needs to be updated.

If anyone knows of a way to get the desired functionality either with VBA or injecting P11 into the Vlookup formula, I would really appreciate the help.


Thanks in advance for any assistance.

 

[sxhall]

Have you tried saving the value of P11 as a variable and then adding in to the VBA code? A crude example below

Dim FileLoc As String

Range("A1").FormulaR1C1 = "=IFERROR(VLOOKUP(B27,'" & FileLoc & "SVR'!$A$11:$K$755,6,FALSE),"")"

 

[calmdrae]

I tried entering you suggestion into my code and it kept throwing error '1004'

I then tried adding it to a brand new sheet manually copying the file location info into B2 and stepping through this code to see if i could at least get it to show the formaula, but I'm getting the same error

Private Sub test()

Dim FileLoc As String

FileLoc = Range("B1").Value

Range("A1").FormulaR1C1 = "=IFERROR(VLOOKUP(B27,'" & FileLoc & "SVR'!$A$11:$K$755,6,FALSE),"")"

End Sub

I have tried similar techniques to your solution, but the [] brackets required around the filename portion of the file location caused headaches.

 

[Osvaldo Palmeiro]

I have tried similar techniques to your solution, but the [] brackets required around the filename portion of the file location caused headaches.

Hi. Maybe you could add the [] brackets required.

Sub AddSQBrackets()
 Dim FileLoc As String, strFileName As String, k As Long
  k = InStrRev([P11], "\") 'find the position of the last backslash from P11 text
  strFileName = Right([P11], Len([P11]) - k) 'get file name with extension
  FileLoc = Replace([P11], strFileName, "[" & strFileName & "]") ' add square brackets to file name
  Debug.Print FileLoc
End Sub

or

Sub AddSQBracketsReducedVersion()
 Dim FileLoc As String
  FileLoc = Replace([P11], Right([P11], Len([P11]) - InStrRev([P11], "\")), "[" & Right([P11], Len([P11]) - InStrRev([P11], "\")) & "]")
  Debug.Print FileLoc
End Sub

For FileLoc both codes should result ~~~> C:\Users\Desktop\WIP\[Stock valuation May 2020.xlsx]

 

[calmdrae]

Hi. Maybe you could add the [] brackets required.

Sub AddSQBrackets()
 Dim FileLoc As String, strFileName As String, k As Long
  k = InStrRev([P11], "\") 'find the position of the last backslash from P11 text
  strFileName = Right([P11], Len([P11]) - k) 'get file name with extension
  FileLoc = Replace([P11], strFileName, "[" & strFileName & "]") ' add square brackets to file name
  Debug.Print FileLoc
End Sub

or

Sub AddSQBracketsReducedVersion()
 Dim FileLoc As String
  FileLoc = Replace([P11], Right([P11], Len([P11]) - InStrRev([P11], "\")), "[" & Right([P11], Len([P11]) - InStrRev([P11], "\")) & "]")
  Debug.Print FileLoc
End Sub

For FileLoc both codes should result ~~~> C:\Users\Desktop\WIP\[Stock valuation May 2020.xlsx]

These work perfectly and can produce the file name that includes the square bracket so I should be able to use this to push the correct file path. Thank you.

I still have the issue with the code below not populating the cell with a formula

       Private Sub test()

Dim FileLoc As String

FileLoc = Range("B1").Value

Range("A1").FormulaR1C1 = "=IFERROR(VLOOKUP(B27,'" & FileLoc & "SVR'!$A$11:$K$755,6,FALSE),"")"

End Sub

I have figured out what is throwing the 1004 error in the above code also. it is the '=' in the Vlookup protion of the code

Range("A1").FormulaR1C1 = "=IFERROR(VLOOKUP(B27,'" & FileLoc & "SVR'!$A$11:$K$755,6,FALSE),"")"

If I delete the '=' sign there is no error and it populates the cell with the correct text, however it is TEXT and isn't processed as a formula. I think the problem that now exisits is how do I get the cell functioning as a formula, is there a character I can preface the = with or do I surround it with brackets or quotes?

 

[calmdrae]

It is probably worth noting if I manually add the equals sign to the formaula inside the spread sheet itself after runnning the macro, the formula functions correctly.

 

[Osvaldo Palmeiro]

Have you tried removing R1C1 from FormulaR1C1 ? ~~~> Range("A1").Formula = "your formula"

 

[calmdrae]

Have you tried removing R1C1 from FormulaR1C1 ? ~~~> Range("A1").Formula = "your formula"

Hiya, I have tested with removing the R1C1 and that wasnt the issue.

The problem as it turns out was there were some missing quoation marks from the VBA formula a the end of the Vlookup

Range("A1").FormulaR1C1 = "=IFERROR(VLOOKUP(B27,'" & FileLoc & "SVR'!$A$11:$K$755,6,FALSE),"""")"

I have added them in and the sheet is now functioning exactly as I desired.

Thanks for your help Osvaldo and SXhall and anyone who took the time to read through to see if they could assist.