When working with a large number of data in Excel, it may be difficult to locate a value or a piece of data. Using VBA code this difficulty can be solved which can save a lot of time. In this article, we will show you five simple examples to find a value in a row and return the column number using VBA in Excel.
How to Find Value in Row and Return Column Number Using Excel VBA: 5 Suitable Examples
In this article, we will demonstrate five suitable examples to find a value in a row and return the column number using VBA in Excel. We will use the following dataset for this purpose.
Example 1: Using MATCH Function to Return Column Number
In this example, we will use the MATCH function to find a value in a row and return the column number using VBA in Excel. The steps are discussed in the following section.
Steps:
- First of all, press Alt + F11 to open the Microsoft Visual Basic for Applications window.
- Then from the Insert tab, go to,
Insert → Module
- As a result, the Module window will appear.
- We want to find the column number for “Donuts”. Therefore, write down the following code in it.
Sub Match_Find()
Dim x As Long
Dim y As String
x = Application.WorksheetFunction.Match("Donuts", Range("A4:J4"), 0)
y = Split(Cells(1, x).Address, "$")(1)
MsgBox "The Column Number is : " & y
End Sub
- Then press F5 to run the code.
- Hence, a message box will appear with the column number.
Read More: How to Return Column Number of Match in Excel
Example 2: Use Input Box to Return Column Number
In this example, we will create an input box to type the data we are looking for and find the column number where the data is present. Read the following steps to learn how to do it.
Steps:
- First, open a Module in the Microsoft Visual Basic window like in the previous example.
- Then copy the code from below and paste it into the Module.
Sub inputbox_Find()
Dim List_of_Food As String
Dim column1 As Range
List_of_Food = InputBox("Name of the Food is")
Set column1 = Cells.Find(What:=List_of_Food, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If column1 Is Nothing Then
MsgBox ("Food Not found")
Else
MsgBox "The Column Number is : " & column1.Column
End If
End Sub
- After that, press F5 to run the code. As a result, an input box will open.
- In the input box, write what you want to look for in the sheet.
- In this example, we are looking for “Yogurt”.
- Then click OK.
- Consequently, a message box will appear with the column number.
- In this example, the column number is 7 (column G) for “Yogurt”.
Example 3: Apply Range.Find Method
Now we will use the Range.Find method to find a value in a row and return the column number using VBA in Excel. The procedure is discussed below.
Steps:
- First of all, pres Alt + F11 to open the Microsoft Visual Basic window.
- Then press Alt + I + M to insert a Module.
- In the Module, type the following formula.
Sub Range_Find()
Dim x As Worksheet
Dim y As Long
Dim z As String
Dim m As String
Set x = Worksheets("Range.Find")
z = "Croissant"
y = x.Cells.Find(What:=z, After:=Cells(1, 1), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column
m = Split(Cells(1, y).Address, "$")(1)
MsgBox "The Column Number is: " & m
End Sub
- Then to run the code, press F5 .
- Hence, a message box will appear. The column number will be displayed inside it.
Example 4: Use Rows Property
We can also use the rows property to find the row number of a value. Keep reading to learn how to do it.
Steps:
- First, insert a Module as we did in the first method.
- Now we want to find the column number for “Coffee”. write down the following code in it.
Sub Column_Find()
Dim x As Long
Dim y As String
x = Rows(4).Find(What:="Coffee").Column
y = Split(Cells(1, x).Address, "$")(1)
MsgBox "The Column Number is: " & y
End Sub
- Finally, press F5 to run the code, and you will get a message box that will display the column number.
Example 5: Utilize StrComp Function
In the last example, we will use the StrComp function to find a value in a row and return the column number. Read the process discussed below.
Steps:
- First of all, open a Module window in the Microsoft Visual Basic for Applications window like in the first method.
- Then copy the following formula and paste it into the Module.
Sub StrComp_Find()
Dim x As Worksheet
Dim y As Long
Dim k As Long
Dim Value_Search As String
Dim Data_array As Variant
Dim m As String
Set x = Worksheets("StrComp")
Data_array = x.Range("A1:J100")
Value_Search = "Waffles"
For k = 1 To 100
If StrComp(Data_array(4, k), Value_Search, vbTextCompare) = 0 Then
y = k
m = Split(Cells(1, y).Address, "$")(1)
Exit For
End If
Next k
MsgBox "The Column Number is: " & m
End Sub
- After that, press F5 to run it.
- Consequently, you will see a message box with the column number in it.
- While using the VBA codes, change the name of the value or data and cell range as per your need.
- In the second method, the column number will appear as 1 for column A, 2 for column B, and so on.
Read More: VBA to Convert Column Number to Letter in Excel
Download Practice Workbook
Conclusion
Thanks for making it this far. I hope you find this article useful. Now you know how to find a value in a row and return the column number using VBA in Excel. Please let us know if you have any further queries, and feel free to give us any recommendations in the comment section below.
