Get row index of dynamic cell

Help Studio
, ,
1

i have one usecase in which i have to copy formula from 1 row & paste to whole row which include some formula Including row index of some dynamic cell(which starts with T).
input sheet:-
column1 column2 column 3
2345 a1+3b a1-3b
1234
T-8456 xy xy
4567
8675
45678
4567
3456
T-4567 xy xy

Output sheet:-
column1 column2 column 3
2345 a1+3b a1-3b
1234 a2+3b a2+3b
T-8456 xy xy
4567 a4+9b a4+9b
8675 a5+9b a5+9b
45678 a6+9b a6+9b
4567 a7+9b a7+9b
3456 a8+9b a8+9b
T-4567 xy xy

Calculating index of row which starts with T is taking more time with For each loop as i have 100 rows.how can i do this usecase efficiently.

2

@neha.kumarief868

If the value beside every T related row is constant then you can copy the values in those cells and then use autofill range activity to fill the formula till bottom

Thwn after filling replace the old values itself using the T as anchor

Cheers

3

value beside every T related row is not constant, i dnt have to touch row which starts with T,i just need the index which is dynamic

4

@neha.kumarief868

So you can try like this…

Create integer variables oldt and newt

And initialize oldt with 2

And create one variable to store the formula from first row lets say str_formula

  1. Use find/replace and find the cel numbers containing T-…the output will be array of cell numbers arrNum
  2. Use a for loop on arrNum and change the type argument to string…and assign a counter variable to the index in the for loop properties
  3. Now inside loop use a if condition with (arr.IndexOf(currentitem) mod 2).Equals(0)
  4. On the then side use assign with newt = Cint(currentitem.Replace("A"))-1
  5. On same then side use a write cell with cell as "C" + oldt.ToString and value as str_formula.replace("A2","A" + oldt.ToString)
  6. On same then side again use a auto fill range with range as "C" + Oldt.ToString + ":" + "C" + newt.ToString
  7. Now on else side use assign with oldt = cint(currentitem.Replace("A"))+1

Hope this helps

Basically I am trying to find the T related cel values and use them as anchor till fill using auto fill range till the next T

Cheers

5

can you explain a bit more,im not able to apply the same in UiPath.

6

@neha.kumarief868

May i know which part you are unable to?

Please show what you have done and where you are facing the issue

Cheers

7

Screenshot (25)
Screenshot (25)806×515 12.9 KB

i have extracted the cell number like above and getting error in if condition
Screenshot (24)
Screenshot (24)1042×553 18.2 KB

:-

8

@neha.kumarief868

First of all the condition you have for where will get the roes with t …that is the reason i asked ypu to use find/replace so that you get the exact indexes…the value sthat you get are not the correct index

And I am not sure what you have in array of codes

Ideally the array should contain the cel values

Cheers

9

i have stored index manually as of now but still it is having error.

Screenshot (27)
Screenshot (27)1027×530 26.4 KB

10

@neha.kumarief868

Please use indexof like this

Array.IndexOf(arrofcodes,item)

Cheers

11

Hi Anil,
Thanks for the response , but thi sline is also failing with same error message(Above one).
newt = Cint(currentitem.Replace("A"))-1
Is it possvible for you to share the code in xaml.

12

@neha.kumarief868

Please use the line like this…previous one was wrong

oldt = cint(currentitem.Replace("A",""))+1

Cheers

13

you have mentioned the sam eformula previously also.

14

@neha.kumarief868

In the previous one I missed giving what to replaceA with…so I added it to replace with blank

Cheers