VBA Button to select source sheet for Vlookup

calmdrae

New Member
Joined
Jun 1, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

sxhall

Board Regular
Joined
Jan 5, 2005
Messages
150
Have you tried saving the value of P11 as a variable and then adding in to the VBA code? A crude example below

VBA Code:
Dim FileLoc As String

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

calmdrae

New Member
Joined
Jun 1, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

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

Well-known Member
Joined
Feb 24, 2009
Messages
689
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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

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

New Member
Joined
Jun 1, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi. Maybe you could add the [] brackets required.
VBA Code:
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

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

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

New Member
Joined
Jun 1, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 24, 2009
Messages
689
Office Version
  1. 365
Platform
  1. Windows
Have you tried removing R1C1 from FormulaR1C1 ? ~~~> Range("A1").Formula = "your formula"
 

calmdrae

New Member
Joined
Jun 1, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

Forum statistics

Threads
1,140,925
Messages
5,703,202
Members
421,280
Latest member
Jaycee01

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