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