Table of Contents
HLOOKUP is a useful function in VBA (Visual Basic for Applications) that allows users to search for a specific value in a horizontal table and retrieve information from a corresponding row. This can be particularly beneficial in automating tasks and managing large sets of data. To use HLOOKUP in VBA, the syntax is as follows: HLOOKUP(lookup_value,table_array,row_index_num, [range_lookup]). The lookup_value represents the value being searched for, the table_array is the range of cells containing the data, row_index_num is the row from which the data will be returned, and range_lookup is an optional argument that specifies whether an exact match is needed or not. Here is an example of how HLOOKUP can be used in VBA: Suppose we have a table with employee names in the first row and their corresponding salaries in the second row. To retrieve the salary of a specific employee, we can use HLOOKUP in VBA. For instance, the code “MsgBox HLOOKUP(“John”, Range(“A1:C2″), 2, False)” will return the salary of John from the table. In summary, HLOOKUP in VBA is a powerful tool that can greatly enhance data handling and automation processes.
Use HLOOKUP in VBA (With Examples)
You can use the following basic syntax to perform a HLOOKUP using VBA:
Sub Hlookup()
Range("H2").Value = WorksheetFunction.HLookup(Range("G2"),Range("A1:E2"),2,False)
End SubThis particular example looks up the value in cell G2 in the columns of A1:E2 and finds the value located in the second row of that specific column and then assigns the result to cell H2.
Note: The last argument of False specifies that we want to find an exact match.
The following example shows how to use this syntax in practice.
Example: How to Use HLOOKUP in VBA
Suppose we have the following dataset in Excel that contains information about various basketball players:
Suppose we would like to look up the team name “Mavs” in the dataset and return the corresponding value in the points row.
We can create the following macro to do so:
Sub Hlookup()
Range("H2").Value = WorksheetFunction.HLookup(Range("G2"),Range("A1:E2"),2,False)
End SubWhen we run this macro, we receive the following output:
The macro correctly returns a value of 22 points for the Mavs.
If we change the name of the team in cell G2 and then run the macro again, it will correctly find the points value for the new team name.
For example, suppose we change the team name to “Rockets” and run the macro again:
Note: You can find the complete documentation for the VBA HLookup method .
Cite this article
stats writer (2024). How can I use HLOOKUP in VBA with examples?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-hlookup-in-vba-with-examples/
stats writer. "How can I use HLOOKUP in VBA with examples?." PSYCHOLOGICAL SCALES, 24 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-hlookup-in-vba-with-examples/.
stats writer. "How can I use HLOOKUP in VBA with examples?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-hlookup-in-vba-with-examples/.
stats writer (2024) 'How can I use HLOOKUP in VBA with examples?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-hlookup-in-vba-with-examples/.
[1] stats writer, "How can I use HLOOKUP in VBA with examples?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use HLOOKUP in VBA with examples?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.