When we talk about Excel and VBA, one of the most important things is to be able to position ourselves and find the data in our text in an easy way.
One very useful option is to find the column index, or a column number, where a certain text or number is located. In the example below, we will show how to do this
Find Column Index in VBA
First thing first, we will input some text into our worksheet. It will simply be the word “Example”, and we will put it in cell B6. We know that the column index of our word is number 2, as it is located in column B.
But if we had a lot of data and various words as examples, or if we had the word “Example” in more than one column, how could we find out the indexes of these columns automatically?
To write our code, we need to access the Visual Basic Editor. To do so, we press ALT + F11 on our keyboard. After that, on the window that appears, we will right-click on the left window and choose Insert >> Module:
Once the new window opens on the right side, we will write this code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Sub ColumnIndex() 'Setting variables Dim Message As String Dim r As Range Dim word As String 'Declaring variables word = Application.InputBox(Prompt:="What is the word you are seeking", Type:=2) Message = "Column(s): " 'For Next Loop For Each r In ActiveSheet.UsedRange If r = word Then 'Define the message Message = Message & r.Column & ", " End If Next r 'Message to appear MsgBox Message & " is the column where the word is located." End Sub |
This is what our code looks like in the editor:
As per comments in the code (marked in green in the picture above), we do the following things:
1 2 3 |
Dim Message As String Dim r As Range Dim word As String |
First, we declare three of our variables: Message, r, and word.
Next, we assign certain values to these variables:
1 2 |
word = Application.InputBox(Prompt:="What is the word you are seeking", Type:=2) Message = "Column(s): " |
Our variable word will be equal to whichever word the user is searching for, and the message that will show in the pop-up window will be just that- „What is the word you are seeking?“.
Variable Message, which is of a string type, will begin with „Column(s):“ text, and we will add the rest of it later, depending on the results.
For the next, we will create For Next Loop, and in that loop, we will have the If function:
1 2 3 4 5 6 7 |
'For Next Loop For Each r In ActiveSheet.UsedRange If r = word Then 'Define the message Message = Message & r.Column & ", " End If Next r |
This loop says that for every word that the user defines in our sheet if it exists, we show the column index number of the column (or columns) where the word is located.
For the final part we create the MessageBox that will show us where the column (or columns) are located:
1 2 |
'Message to appear MsgBox Message & " Is the column where the word Is located." |
When we execute our code by clicking F5 on our keyboard while in the editor, this is the first message that pops up:
The user has several options here- he can input the word or he can click and choose a certain cell. Of course, the second act seems pointless, as then the user would know where the cell is located. So we will insert the word „Example“ in the text field, and this is the result we would get:
The picture above shows the message that column 2 is the column where is keyword is located.
If we would go on and input the word “Example” in column G as well (for example cell G2) when we execute the code, we would get a similar message:
Of course, in this case, two columns- 7 and 2, are presented in the message.
This code can use a little tweaking, for example, if the user does not enter anything or if he/she enters a word that cannot be found in the sheet, a special message would appear. As for this code, the user will only get the following message:
Which would show that there are no columns where the searched word is located. For the sake of this exercise, where the focus is on finding the column index or indexes, we can consider it good enough.