Find Range of cell using column Name

[Shikhar_Tandon]

Hi Friends,

Urgent help needed , Any leads would be highly appreciated.
My problem is:

This is an excel sheet i want to find the Cell range index of column name - “Item”, for it i am using

Convert.Tochar(65+dt.columns.IndexOf(“Item”)),
but every time this is giving me range value “E”, i think it is not counting blank cells,
any suggestions that how can i get the original range - “G”,
and this is the case for any column i want to take out the range.
@ppr, @Palaniyappan, @ClaytonM
Feel free for clarifications .!!
Thanks

[Lakshay_Verma]

Please share headers of your excel file

[Shikhar_Tandon]

headers are , unit, cost,total,value,item,order no, id,upc,mnb,local,desc

@Lakshay_Verma

[Lakshay_Verma]

If there’s any blank, try using Lookup Range in Excel Application Scope, It will give you exact cell value of this particular column name

[Lakshay_Verma]

@Shikhar_Tandon
LookupValue.xaml (6.1 KB)

hope this helps Happy Automation

[Shikhar_Tandon]

Thanks for the solution @Lakshay_Verma

but is their a way using any query like i used or function , that i can use , i dont want to use look up range basically due to some job requirements .
Thanks !!

[AkshaySandhu (AkshaySingh Sandhu)]

Hello @Shikhar_Tandon
your requirement is clear but your data is not.
As I can see from your screenshot your actual data range starts from C4 (I assuming that it is your start range when you are reading this excel)

but when you do 65 + column.index then there is assumption that your data range starts from A1 [as char 65 represents alphabate A].

kindly let us know how you are reading your excel file, also let us know if start range is dynamic or not.

[Shikhar_Tandon]

yes @AkshaySandhu my start range is dynamic , i am reading the excel by passing the first column name that is here is " unit " in a look up range which gives me the starting position or range of data.

can you suggest some solution .

thanks for the rply !

[balupad14 (Balamurugan)]

HI @Shikhar_Tandon,

Here is an activity called “Find”. Using this activity you can able to find the string in the excel and it will return the CellInfo and row index. Below I have attached the sample for you.

UiPath Go ! BalaReva EasyExcel Activities

Hi, A situation to find and replace the text inside the Excel. For that purpose, I have developed some activities below. Find - Find the text inside the sheet. It will return the cell info as an array string and row indexes. FindReplace - Find and replace the text inside the Excel sheet. [image] [image] Regards Balamurugan.S

Sample :
Shikar.zip (33.0 KB)

Here is the output.

Note:
If you know the range , you can specify it in the Cell Range Property.

Thank you
Balamurugan.S

[AkshaySandhu (AkshaySingh Sandhu)]

in this case instead of static 65 use dynamic value like below

ASC(System.Text.RegularExpressions.Regex.Replace(strUnitLookUpVal,"[^A-Z]",""))

so you final line of code should look like

Convert.Tochar(ASC(System.Text.RegularExpressions.Regex.Replace(strUnitLookUpVal,"[^A-Z]",""))+dt.columns.IndexOf("Item"))

strUnitLookUpVal = earlier lookup value that you got for “Unit”

[Shikhar_Tandon]

Thanks for the solution @AkshaySandhu but this is giving me , attached in SS .

Thanks

[Shikhar_Tandon]

Btw thanks for the idea @AkshaySandhu , i got what i wanted by doing some modifications on my side cheers !