VBA to return column letter (1 Viewer)

[tmyers]

I made a simple Excel sheet that people in my department use to send out our weekly bid list, but have found that people keep tampering with the source data (an exported file from Smartsheet's where are bid list is normally maintained) and it keeps messing up my code since I have row and column values hardcoded.

What would be the proper way to search the first row and return the column number when a specific string is found? The searched for value will always be a text string that will never change (I have that aspect locked in the source file) but its position is subject to change. I have been bouncing between way too many languages and applications to keep things straight and its been awhile since I have coded in excel.

So essentially, search row 1 for text string and return column.:

 

[Gasman]

Quick search of

for text string and return column - Yahoo Search Results

gives https://www.exceldemy.com/excel-find-text-in-range-and-return-cell-reference/ as one of the links

Just noticed my search engine has changed?

Google

search row 1 for text string and return column - Google Search

 

[tmyers]

I suppose off to the side on my primary sheet, I could just do a simple formula to return it. That would honestly be easier then coding the entire thing.

 

[Gasman]

I suppose off to the side on my primary sheet, I could just do a simple formula to return it. That would honestly be easier then coding the entire thing.

I'd write a function, then I could use it for any string I was looking for?

 

[tmyers]

After reading more on the subject (and digesting more coffee) I figured it out.

To find the column number I used:

ColumnNum = WorksheetFunction.Match("Project Sum Hierarchy Helper", ws.Range("1:1"), 0)

To then convert that to the column letter (since some code doesn't work with the number, I used:

ColumnLet = Split(Cells(1, ColumnNum).Address, "$")(1)

 

[Gasman]

Well, one of those links did all that in one go?
To each their own.

 

[tmyers]

It did and this is just how I ended up writing it out.
I still very much thank you for your help Gasman!