Getting column Letter from Value in header

[Automater999]

Hi, I want to get the column letter based on the value in the column header.

I have the below which works up to column Z. Then after that when the columns change to AA, AB etc it fails. Any idea how to fix this?

Convert.ToChar(ExtractDataTable.Columns.IndexOf(myVariable)+65).ToString

[ermanoj3101 (MANOJ)]

Hi @Automater999 ,

Use below code in your invoke code:

Dim dividend As Integer = columnNumber
Dim modulo As Integer

While dividend > 0
   modulo = (dividend - 1) Mod 26
   columnName = Convert.ToChar(65 + modulo).ToString() & columnName
   dividend = CInt((dividend - modulo) / 26)

End While

Note: Use argument to pass in invoke code shown below:

[Automater999]

What is the index being declared?

I want to be able to get the column letter based on the contents in row 1. so if its ADAI i want to get column C

[ermanoj3101 (MANOJ)]

Hi @Automater999 ,

Here index is the column number, if you just pass the column number it will convert it into Excel column( In Alphabets).
But i got, you want it on the basis of value in the excel. Let me do something on that also.

Thanks

[Srini84 (Srinivas Kadamati)]

@Automater999

You can use For Each activity and write as Dt.Columns
Declare a variable for For Each Index also
Use If Condition as Item = ADAI and then you can place the invoke code function given by Manoj

Hope this may help you

Thanks

[ermanoj3101 (MANOJ)]

Hi @Automater999 ,

Try this to get index number of column.

[Automater999]

Will this code give me the column letters past Z? what if i have like the below?

[ermanoj3101 (MANOJ)]

Yes, you can use it.
Test.xaml (5.0 KB)

[prasath17 (Guru)]

@Automater999 - You can use this logic…This will cover from “A to AZ”…