Getting column Letter from Value in header

Help Studio
, ,
1

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

2

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:

image
image751×424 16.3 KB

3

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

image

4

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

5

@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

6

Hi @Automater999 ,

Try this to get index number of column.

image
image899×488 11.1 KB

8

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

image

9

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

10

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

image