[RESOLVED] Get Column Letter In Excel

[Sean04]

Hey,

I'm using the following code to search an excel document for the highest value within a range. What I need is to get the column letter that corresponds with that MAX value.

xlTemp.Workbooks.Open("http://somesite/" & String.Format("{0:yyyyMMdd}", DateTime.Today.AddDays(7)) & ".csv")
xlTemp.Visible = False
'Search the specific range and find the MAX value
xlRange = xlTemp.Range("A15:X17")
xlMax = xlTemp.WorksheetFunction.Max(xlRange)
peakArrayList.Add(xlMax)
xlTemp.Workbooks.Close()

Thanks,
Sean

[cicatrix]

I always simply add 64 to the column number (or 65 I don't remember whether columns start with 1 or 0) and use CHR function.

[Sean04]

Do you by any chance have an example?

[cicatrix]

Of what? CHR function?

[Sean04]

Well the entire thing. Something I can try and implement within my code.

[Tommy.net]

I always simply add 64 to the column number (or 65 I don't remember whether columns start with 1 or 0) and use CHR function.

the columns start with 1

[cicatrix]

Oh, I don't think the max function will help you then, since it simply returns the maximum value and does not return the address. You should look for your maximum value using the Range.Find method:

'  I presume that xlMax is declared As Range because Find method returns Range
xlMax = xlRange.Find(xlTemp.WorksheetFunction.Max(xlRange)).Address

Alternatively you could iterate through each of the cells in your xlRange and look for the maximum value yourself.

[Sean04]

Thanks! It works
Is there any way to just get the column or just the row?
Right now its giving me the exact like $S$19

[Sean04]

What i'm trying to do is to define each column as an hour (eg. column A would be h1, column B would be h2, and so on...)

[cicatrix]

I don't undertand - here, the column letter is "S", you can get the column number by using xlMax.Column property or the row number with xlMax.Row.

Or you don't know how to extract 'S' from a string like "$S$19"?

' Dirty hack LOL
Dim ColumnLetter As String = CHR(xlMax.Column + 64)

[Sean04]

xlMax.Column doesn't work. I want to just go by the column letter.

If xlMax.Column = "$A" Then
lbl1.text = "hour1"
ElseIf xlMax.Column = "$B" Then
lbl2.text = "hour2 
End If

I realize this doesn't work but its just an example of what im looking for.
I need to get the letter alone because like I said, right now its giving me the exact location such as $S$19

Thanks,
Sean

[Sean04]

Or should I try and use that hack

[Sean04]

nvm got it!

If xlMaxLocation.ToString Like "$S*" Then
    xlMaxLocation = "h19"
End If

Thanks,
Sean

[cicatrix]

This 'hack' is perfectly normal and should work.
It is much faster too (I mean processor time).