#Vlookup in excel vba tutorial code#
The code above does not like me passing a string variable to the lookup when the data in Excel is numeric. You have to pass the correct data type to the VLookup if you are passing a variable. When you run the code, Sheet 2 will be populated as follows: Things to look out for StrSurname = (intAccount, Sheet1.Range("A5:H30"), 4, False)ĪctiveCell.Offset(0, 1).Value = strFirstName & amp amp " " & amp amp strSurname StrFirstName = (intAccount, Sheet1.Range("A5:H30"), 3, False) There may be an occasion when you need Excel to loop through your code and look up values and then pass those values to a separate sheet.įor example, using our list of bank accounts, we may have another Excel sheet with the account numbers listed in them, and we wish to look up the names of the customers against each account number. Populating a list in a separate sheet using VLookup When you run the procedure, the following input box will appear MsgBox "The current balance is " & amp amp curBal IntA = InputBox("Please enter the account number")ĬurBal = (intA, Range("A5:H30"), 8, False) Instead of hard-coding the account number to lookup, the macro could ask the user to input the account number, and then return the available balance. This type of lookup could be incorporated into a macro that interacts with the user.
The procedure above would look up account number 568 and return the Average Monthly Balance for that account. Using the example above, we want to look up the Average Monthly Balance for a certain account number.
Result = ( lookup_value, table_array, col_index_num, ) Using VLookup to look up a value
0 kommentar(er)
