Hi guys,
I want to make my Lookup DT dynamic - meaning - I want the Target columnindex to dynamic loop through all the columns and gradually enter data into my excel file.
I tried to use the following: cint(DTSelEq.Rows.IndexOf(row)+3) - but it throws an error.
Kindly guide how to work it out.
You should check DTSelEq.Rows.IndexOf(row)+3 is within the size of columns of DTCrisil.
You may use write line activity to see what that actually is. (Don’t forget to add .ToString inside write line to match type)
Alternatively, you can add break point to Lookup Data Table activity and check the evaluation from Immediate Panel while in Debug mode.
BTW, Write Rante activity is much faster than writing into cells one by one.
Hi Yuji,
Yes sorry I checked and it should have been DTCrisil.Rows.IndexOf(row)+3 - however, the lookup datatable still does not loop through when I enter this variable into the Target ColumnIndex.
Any idea how I can loop it?
As for the Write Range activity, since its inside a FOR EACH loop, I used the write cell only, however will keep it in mind thanks!
@Mayank_Agarwal
How many columns are there in DTCrisil?
What is the value of DTCrisil.Rows.IndexOf(row)+3 when it fails?
@yuji.katayose
There are 33 columns in DTCrisil.
It is showing a value of -1 (I don’t know why. It is incorrect. You are right.)
Ideally, it should’ve shown 3,4,5… and so on. But its stuck at -1.
From documentation, IndexOf method returns -1 if the row is not found in the collection.
OH!!
Crap. Any idea why it is not able to find the row? Is there an error with my formula?
Any idea Yuji?
How do you define row? Is it really from DTCrisil?
DTCrisil.Rows.IndexOf = This is the basic syntax
(Row) = This is simply the row variable that is part of the FOR EACH row loop (For each ROW in DTSelEq.
So, whenever the For each ROW variable increases by 1, the ROW inside DTCrisil will also increase.
Thats my logic for putting row there.
row has to be from DTCrisil not from DTSelEq.
Oh my…
Thank you so much @yuji.katayose that makes total sense.
Do you think if I create a for each loop for row in DTCrisil and then put it inside the target Column Index, will that work?
Or is there any other solution you suggest?
cint(DTSelEq.Rows.IndexOf(row)+3)
This is your first code. How about revert your code to This. You said row is member of DTSelEq. So this code will make sense.
What you need to check is that DTSelEq.Rows.IndexOf(row)+3 is within the size of columns of DTCrisil as I said before.
OOOOH
Yes I got it! Understood Yuji! Thank you. Lemme try this and get back.
Hey @yuji.katayose
While I have entered the previous syntax in the target Column, but how do I make the write cell part dynamic too?
I need a syntax that will dynamically change columns (from C to D and so on) once its done with one Column.
Attaching a screenshot. I need a dynamic variable for [coln, row no.] like [C6, etc.]
One way to achieve dynamic row indexing is using array, something like arr= {“A”, “B”, …, “AA”,…} and accessing elements by row index, like arr(i).
But this is kind of ugly and row size is limited to the size of array. I do not want that array to be hardcoded in the workflow, because it’s intolerant to the increase in row size.
So, I would rather store data as DataTable and use write range to store all data at once at the end of the loop as I said before.
Let’s say I do that and use write range to write the stored data into a range.
Now from that range, how do I paste that into DTSelEq datatable?
Opposite to write range is read range.
If you want to learn how to manipulate datatable, you can use an example on our docs.
We also have online learning material, which covers datatable and many other things developers should know before building a workflow.
Ummm
Okay @yuji.katayose thanks for all your help.
I think I got some of it, lemme just try it out thanks!
