[RESOLVED] Get Column Letter In Excel
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.
Code:
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
Re: Get Column Letter In Excel
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.
Re: Get Column Letter In Excel
Do you by any chance have an example?
Re: Get Column Letter In Excel
Re: Get Column Letter In Excel
Well the entire thing. Something I can try and implement within my code.
Re: Get Column Letter In Excel
Quote:
Originally Posted by
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.
the columns start with 1
Re: Get Column Letter In Excel
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:
vb Code:
' 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.
Re: Get Column Letter In Excel
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
Re: Get Column Letter In Excel
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...)
Re: Get Column Letter In Excel
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)
Re: Get Column Letter In Excel
xlMax.Column doesn't work. I want to just go by the column letter.
Code:
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
Re: Get Column Letter In Excel
Or should I try and use that hack :p
Re: Get Column Letter In Excel
nvm got it!
Code:
If xlMaxLocation.ToString Like "$S*" Then
xlMaxLocation = "h19"
End If
Thanks,
Sean
Re: [RESOLVED] Get Column Letter In Excel
This 'hack' is perfectly normal and should work.
It is much faster too (I mean processor time).