Find Value in Row and Return Column Number Using VBA in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

excel vba find value in row and return column number


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

Return Column Number Using MATCH Function

  • Then press F5 to run the code.
  • Hence, a message box will appear with the column number.

excel vba find value in row and return 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.

Use Input Box to Return Column Number

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

Apply Range.Find Method to Find Value in Row and Return Column Number Using VBA in Excel


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.

Use Rows Property to Find Value in Row and Return Column Number Using VBA in Excel


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.

Notes
  • 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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alif Bin Hussain
Alif Bin Hussain

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Civil Engineering from Bangladesh University of Engineering & Technology (BUET). I am a fresh graduate with a great interest in research and development. I do my best to acquire new information and try to find out the most efficient solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo