Find a column through Macro

[omair1051992]

I am trying to write a Macro that will identify a particular column by its field header (i.e. first row) and will run a specific set of code on that particular column.

My current code looks like this, coverting New York to NY:-

Cells.Replace What:="New York", Replacement:="NY", LookAt:=xlPart, SearchOrder:=xlByRows

This code applies to 'New York' found in the entire sheet. I need to identify a column that is named 'State' and then make the code run ONLY on that column.

 

[omair1051992]

Or to make it simple, how do I set the code to run only for a fixed column, say F, instead of making it search for the column beforehand?

 

[BiocideJ]

Or to make it simple, how do I set the code to run only for a fixed column, say F, instead of making it search for the column beforehand?

Don't make it simple.

col = Application.WorksheetFunction.Match("State", Range("1:1"), 0)
Columns(col).Replace What:="New York", Replacement:="NY", LookAt:=xlPart, SearchOrder:=xlByRows

This will dynamically find State in row 1 and only replace "New York" in that column.

If you WANTED to do it statically you would just need to know the range you wanted to run the search on.
In this case it would be F:F

Range("F:F").Replace What:="New York", Replacement:="NY", LookAt:=xlPart, SearchOrder:=xlByRows

 

[omair1051992]

Thank you so much. That works perfectly.

However, when there is no exact word 'State' as a field name, the Macro gives a runtime error 1004 'Unable to get the match property of the WorksheetFunction class'.

How can I make it give out a simple message that no field named 'State' was found?

Plus, what if I had two possible options of a field header, 'State' or 'State/Province' for example? How can I make the Macro search for both but run on only what is found?

 

[BiocideJ]

See... that is why I voted against simple. LOL

This should do everything you are wanting with the new requirements.

Private Sub test()
Dim Found As Boolean
Found = False
For Each col In [1:1]
    If col.Value Like "*State*" Then
        Columns(col.Column).Replace What:="New York", Replacement:="NY", LookAt:=xlPart, SearchOrder:=xlByRows
        Found = True
    End If
Next
If Not Found Then MsgBox "No field named 'State' was found in Row 1!", vbExclamation + vbOKOnly
End Sub

Note that this will also replace multiple columns if there are multiple columns with the word State in row 1.

 

[omair1051992]

Thanks! That did correct all problems.

 

[BiocideJ]

No problem. Thanks for replying that it worked for you.