stevenbehr
New Member
Hi There,
Currently I am building a Inventory database with a Add Order module in VBA.
I understand the OFFSET Data Validation in Excel workbook but what I don't understand is how to in VBA how to input an offset formula to lookup from a product (AccualValueStockLoc) or Sheet7 and to select a combobox (Arow5) be able to view multiple locations from the dropdown combobox.
You will see in my example JPG file, the module in the background and the spreadsheet to which I am using as an example :ItemID 85070481 has 2 locations with 2 different QTY's, I need a formula that offsets from ItemID
to select multiple locations + a formula to show the QTY from the location. - the below is my VBA formula for Vlookup but look up only 1 location.
Me.Arow5 = Application.WorksheetFunction.VLookup(Me.Arow4, Sheet7.Range("ProductQtyLocFind"), 4, 0)
This is my full VBA formaula
Thanks
Steve
Currently I am building a Inventory database with a Add Order module in VBA.
I understand the OFFSET Data Validation in Excel workbook but what I don't understand is how to in VBA how to input an offset formula to lookup from a product (AccualValueStockLoc) or Sheet7 and to select a combobox (Arow5) be able to view multiple locations from the dropdown combobox.
You will see in my example JPG file, the module in the background and the spreadsheet to which I am using as an example :ItemID 85070481 has 2 locations with 2 different QTY's, I need a formula that offsets from ItemID
to select multiple locations + a formula to show the QTY from the location. - the below is my VBA formula for Vlookup but look up only 1 location.
Me.Arow5 = Application.WorksheetFunction.VLookup(Me.Arow4, Sheet7.Range("ProductQtyLocFind"), 4, 0)
This is my full VBA formaula
Code:
Private Sub Arow3_Change()
On Error Resume Next
Me.Arow2.RowSource = ""
Me.Arow4 = Application.WorksheetFunction.VLookup(Me.Arow2, Sheet5.Range("Data"), 2, 0)
Me.Arow6 = Application.WorksheetFunction.VLookup(Me.Arow2, Sheet5.Range("Data"), 6, 0)
Me.Arow5 = Application.WorksheetFunction.VLookup(Me.Arow4, Sheet7.Range("ProductQtyLocFind"), 4, 0)
Thanks
Steve
Attachments
Last edited by a moderator: