• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Offset Formula in VBA module ComboBox

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

  • Order-Matic 6.0.xlsm
    413.1 KB · Views: 9
  • OFFSET.jpg
    OFFSET.jpg
    114.9 KB · Views: 11
Last edited by a moderator:
Back
Top