Finding dynamic cell position and read cell position +1

Capture

Hi All,
Cell position of Title and value can be change in my project, but value is always under the constant title. Since the value is variant I search the title’s cell position with look up range activity. How can I read the cell position +1 in this scenario ?

@Palaniyappan
@loginerror
@HareeshMR
@AshwinS2
@Pablito

Please help me guys

Hi
–use a excel application scope and pass the filepath as input
–inside the scope use a READ RANGE where in the sheetname mention the sheet and range as “A1” and get the output with variable named dt…also disable the ADD HEADERS property
–now use a for each row activity and pass the above variable dt as input
–inside the loop use a writeline activity like this
"The cell position of value is "+ IF(row.ItemArray.Contains(“title”),Convert.ToChar(Array.IndexOf(row.ItemArray.Contains(“title”)+1+65).ToString+(dt.Rows.IndexOf(row)+2).ToString,String.Empty)

Cheers @Goktug_Gumuslu

1 Like

thanks for reply,
I followed the steps you suggested

when I write this I get a compiler error which write “Option Strict On disallows implicit conversions from ‘Boolean’ to ‘Integer’.”
What do you suggest I do to fix this error.

1 Like

"The cell position of value is "+ IF(row.ItemArray.Contains(“title”),(Convert.ToChar(Array.IndexOf(row.ItemArray,“title”))+1+65).ToString+(dt.Rows.IndexOf(row)+2).ToString,String.Empty)

this is the expression
sorry typo mistake
Cheers @Goktug_Gumuslu

1 Like

Is it working
Cheers @Goktug_Gumuslu

1 Like

gives the same error unfortunately,

by the way
my real excel files look like this
Capture

I need to take Operator Reservation Number as a variable, because I will enter this variable to our ERP program.
capture1

I can find the cell position of Operator Reservation Number title, but ı need to read the cell under that cell.
For instance my title cell position is H14 the value which ı search is always below the title cell in this example it is H15.

is it possible ?

will the ARRIVAL DATE always starts from C3
@Goktug_Gumuslu

1 Like

can be different for each excel

Fine
same steps but with changes in expression alone
–use a excel application scope and pass the filepath as input
–inside the scope use a READ RANGE where in the sheetname mention the sheet and range as “A1” and get the output with variable named dt…also disable the ADD HEADERS property
–now use a for each row activity and pass the above variable dt as input
–inside the loop use a writeline activity like this
"The cell position of value is "+ IF(row.ItemArray.Contains(“Operator Reservation Number”),(Convert.ToChar(Array.IndexOf(row.ItemArray,“Operator Reservation Number”))+1+65).ToString+(dt.Rows.IndexOf(row)+2).ToString,String.Empty)

Cheers @Goktug_Gumuslu

1 Like

"Compiler error(s) encountered processing expression ""The cell position of value is “+ IF(row.ItemArray.Contains(“Operator Reservation Number”),(Convert.ToChar(Array.IndexOf(row.ItemArray,“Operator Reservation Number”))+1+65).ToString+(dt.Rows.IndexOf(row)+2).ToString,String.Empty)”.
Operator ‘+’ is not defined for types ‘Char’ and ‘Integer’."
gave such an error

1 Like

ok got it
""The cell position of value is “+ IF(row.ItemArray.Contains(“Operator Reservation Number”),(Convert.ToChar(Array.IndexOf(row.ItemArray,“Operator Reservation Number”)+65)).ToString+(dt.Rows.IndexOf(row)+2).ToString,String.Empty)”

Cheers @Goktug_Gumuslu

1 Like

By using Lookup Range you will get E4
Then using regex—say u will get numbers say RegexNum=4 and RegexWord then u can use as it in assign for increment it Number=Cint(RegexNum)+1
Then By using CellValue=RegexWord+Number
Sequence.xaml (6.0 KB)

1 Like

For Your Reference
Sequence.xaml (5.7 KB)

2 Likes

-when I used Lookup Range I get output which is H4 and I assign lookupvalue variable
I am confused about Regex operation should I create new variable which name is RegexNum and assign RegexNum=4 can you please explain in detail

I think we are approaching a solution
thanks for reply

By using different Regex for Number and Word store them in different variable
so by using number u will get number say 5 so you can then increment and use it along with the Word regex output say H And 5+1=5 H6

1 Like

Capture

It doesnt work, thanks for your effort.

its working thank you very much

Welcome!!!

1 Like

sorry to rewrite, if the cell position is double-digit for instance F14 regex operation which your solution gives F2 .

how can ı solve this problem ??
@ImPratham45