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 ?
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
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.
"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
gives the same error unfortunately,
by the way
my real excel files look like this
I need to take Operator Reservation Number as a variable, because I will enter this variable to our ERP program.
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 ?
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
"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
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
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)
-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
It doesnt work, thanks for your effort.
its working thank you very much
Welcome!!!
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
Anil_G
ashokkarale
jinal.shah
Gautham_Pattabiraman
postwick
chandreshsinh.jadeja
vrdabberu
Ajay_Mishra
sven.wullum1
Vyshnavi_Nalumachu