See next value in "For Each Excel Row"

[Nils9]

Hello everyone,

I have an excel file, and I’m using “For Each Excel Row” to “browse” through one of my row.
Therefore, I have assign a variable such as “Var = CurrentRow.ByIndex(0)” which give me the value of the row I’m on.

However, I would like to be able to have the next value while still being in my first iteration.
I was thinking about something like "CurrentRow.ByIndex(1), but this gives me the value of the next row, and not the second value on my current row.

What should I do ?

Thank you!

[mkankatala (Mahesh Kankatala)]

Hi @Nils9

In the value that you have given between the braces 0 or 1. It indicates the index of the Column. If you will give 0 it will give you the 1st column value in the Current row and 1 will give the 2nd column value.

In the next iteration you will get the next row value.

Hope it helps!!

[Nils9]

Hi @mkankatala,

Thanks for your quick answer.
I would like to be able to get the next row value but in my current iteration. How could I do that ?
(I want to compare my current value with the next one).

Thanks!

[Yoichi]

Hi,

Can you try as the following?

strAddress = CurrentRow.ByIndex(0).Address

Then, cell address to read is as follows:

Excel.Sheet("Sheet1").Cell(System.Text.RegularExpressions.Regex.Replace(strAddress,"\d+",Function(m) (CInt(m.Value)+1).ToString))

Main.xaml (11.9 KB)

Regards,

[Nils9]

@Yoichi thanks for your answer.

I tried it, and it still gives me the first value of my row.
I tried to edit a bit your querry but didn’t manage to find a solution.

Thank you for your help.

[mkankatala (Mahesh Kankatala)]

You can use the Read cell value activity and give the below one in the cell field,

Excel.Sheet("Sheet1").Cell("A"+(CurrentIndex+2).ToString)

In the Save to field create a variable to store the next row value.

Check the below image for better understanding,

Hope it helps!!

[Yoichi]

Hi,

I’d like to confirm your requirement.

If currentRow is 1 and you get “A” from CurrentRow.ByIndex(0), which do you need B or C?

If C, the above logic will work. Can you share your current workflow?

Regards,

[ppr (Peter Preuss)]

With the involvement of currentindex and the Cell Access Idea from Yoichi we can do:

Form excel:

we can see that at currentRow (K1) we did read next row (K2)

And can also dynamize the A calculation, when other cols are to use

[Nils9]

Thanks to all of you for your help !

@Yoichi, I confirm that I was looking for C, and I had the exact same code as you, but it still gave me the same value as A.

@mkankatala your last code is perfect and helped me to get the next value, thank you very much!

@ ppr I’ll take a look at the answer too, maybe it’s another way to doing it.

Anyway, thanks to you three for your quick reactivity!

Nils

[mkankatala (Mahesh Kankatala)]

I hope you find the solution. If Yes, Make my post Mark as solution to close the loop… @Nils9

Happy Automation!!

[ppr (Peter Preuss)]

To Approach it with an alternate:

• Check if working at datatable level is maybe more fitting to your over-all goal
  OR
• check the parallel usage of the corresponding datatable for the next row value calculation

Main Building Blocks:

Which we would use for a more adapted modelling