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.

## Download Practice Workbook

Download this practice workbook to exercise while reading this article.

## 5 Suitable Examples to Find Value in Row and Return Column Number Using VBA in Excel

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 (5 Useful Ways)**

### 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**”.

**Read More: ****Excel VBA: Get Row and Column Number from Cell Address (4 Methods)**

**Similar Readings**

**Convert Column Letter to Number Chart in Excel (4 Ways)****How to Convert Column Number to Letter in Excel (3 Ways)****VBA to Convert Column Number to Letter in Excel (3 Methods)**

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

**Read More: ****VBA to Use Range Based on Column Number in Excel (4 Methods)**

### 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**.

**Read More: ****Excel VBA: Set Range by Row and Column Number (3 Examples)**

### 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: ****How to Reference Cell by Row and Column Number in Excel (4 Methods)**

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