Help declaring columns based on location of value

[shane_aldrich]

Right now in cell AE1 the value = "SUM", but the word "SUM" floats in row 1

I'd like a macro that looks through row1, and gives me the column letter that the word "SUM" resides in

sCol_1 As String

sCol1 = (Where ever the word "SUM" is located in row 1)

not sure if this is the best way

Thanks

 

[davesweep]

Hi,

How's this for you?

Sub FindSum()
Dim iCol_1 As Integer, sCol_1 As String

iCol_1 = Application.WorksheetFunction.Match("SUM", Range("1:1"), 0)
sCol_1 = Left(Cells(1, iCol_1).Address(1, 0), InStr(1, Cells(1, iCol_1).Address(1, 0), "$") - 1)

End Sub

 

[Jerry Sullivan]

Hi Dave,

Are you sure the task you're doing requires you to have the Column Letter?

Sometimes people ask how to convert Column Numbers to Letters, because they want to use the Letter to build a Range Address Reference. That typically isn't necessary though since within VBA you can use the Column Number instead of the letter.

As a simplified example, if the text "SUM" is in H1 and your task is to read the value below it in H2,
you could use VBA code to build the string "H2" for: Range(sCol_1 & 2).Value
but you would save some steps by simply using: Cells(2, iCol_1).Value