vlookup - excel vba

[thedm]

Can some help me please.

A button that will vlookup another excel file and ask you to input (in bold) the following data into a form

Form will pop up and ask you the following:

scenario:
lookup_value - default as from J up to the last row of the active base workbook
table array start, [input box] or [drop down list] - <letter>, need to type the letter or select from drop down list
table array end - ZZ (end) as default up to the last row "hard coded" as ZZ
column index number [input box] or [drop down list] = <integer> (start), need to type the number [input box] or select from drop down list
range_lookup - default as false

files:
one base worksheet.
second worksheet to lookup must be in *.xls; *.xlsx; *.xlsm;

result:
all lookup research will go to "Column R" of the base workbook.

 

[MCLIFTO8]

You want a userform for the majority of that work.

MS VBA reference, userform object

two tutorials that have helped me in the past.
VBA UserForm Controls - A Guide for Everyone - Excel Macro Mastery
Userform in Excel VBA

Put a submit button on the form.
Then inside the onclick event for the button run your code that checks the inputs and performs the lookup.
Events

 

[thedm]

You want a userform for the majority of that work.

MS VBA reference, userform object

two tutorials that have helped me in the past.
VBA UserForm Controls - A Guide for Everyone - Excel Macro Mastery
Userform in Excel VBA

Put a submit button on the form.
Then inside the onclick event for the button run your code that checks the inputs and performs the lookup.
Events

Thank you.

I was able to came up with the codes below without error.

If InStr(1, strFile, "LIQUOR INVENTORY") Or InStr(1, strFile, "Liquor Inventory") Then

Workbooks.Open strFile

Set dataWbk = Workbooks.Open(fileName:=strFile, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set goalsWs = ThisWorkbook.Worksheets("COUNT SHEET")
Set dataWs = dataWbk.Worksheets("Inventory Detail")

dataWbk.Activate

LookupColumnLetterUno = Application.InputBox("Enter column LETTER from ITEM DESC", "Input a Letter Only", , , , , , 2)
LookupColumnIndexNoUno = Application.InputBox("Number of columns from ITEM DESC to QOH", "Input a Number Only", , , , , , 1)

goalsLastRow = goalsWs.Range("J" & Rows.Count).End(xlUp).Row
dataLastRow = dataWs.Range(LookupColumnLetterUno & Rows.Count).End(xlUp).Row

Set DataRng = dataWs.Range((LookupColumnLetterUno & "5:Z") & dataLastRow)

For x = 2 To goalsLastRow
    On Error Resume Next
    goalsWs.Range("R" & x).Value = Application.WorksheetFunction.VLookup( _
        goalsWs.Range("J" & x).Value, DataRng, LookupColumnIndexNoUno, False)