Find a cell position in Data Table

[Suchi3190 (Suchita Shaniware)]

I tried it… its not working

[AkshayS (Akshay Srinath)]

Hi @Rajtk7,

Check the following Workflow, you might get an Idea.

Find Cell Position.xaml (16.3 KB)
Sample_data.xlsx (8.9 KB)

[ashutoshkhantwal]

@ClaytonM

For Example,

rowIndex =(datatableVariable.Rows.IndexOf(datatableVariable.AsEnumerable().Where(Function(row) row(0).ToString.Contains(“word”)).ToArray()(0))+1).ToString

Hello , I had a similar kind of problem which was resolved using this. Thanks

[Suchi3190 (Suchita Shaniware)]

its done by using some other workaround.Main.xaml (11.7 KB)

[MAHESH1 (MAHESHKUMAR JV)]

@Suchi3190

Find a cell position in Data Table

I have one query How to clear the contetnt of whole column of datatable.

Can you please post the query here.

Because you told the file is in dictionary format.

Find a cell position in Data Table

I am using the file as dictionary and that file column i want to clear. is it problem?

Regards,
Mahesh

[AKY (KyNhongDiMua)]

Hi,

Please refer this topic below if you want to find a cell position in excel file with “invoke VBA”:

How to put variables into the function with "invoke VBA"?

Hi all, I am using the VB function to search the cell address in excel. This is my program VBA: [image] Some function is ok like FindAddressOfActiveCell(). But when using a function that has variable values put in is not good. I tried many times but failed. [image] Please help me in learn. Thank all.

[harinathreddy.yn (Hari)]

Hi can you help me in assigning a value to a cell in data table and I know the column name and row index

[arivu96 (Arivazhagan A)]

Hi @harinathreddy.yn,
use assign activity

dt.rows(rowvalue)("Columnname")="your value"

Regards,
Arivu

[harinathreddy.yn (Hari)]

Thanks for quick response, but I am receiving below error
" Assign : Exception has been thrown by the target of an invocation"

[harinathreddy.yn (Hari)]

[harinathreddy.yn (Hari)]

Hi Arivu96

I got the answer and I used below format.
dt.rows(rowindex).item(column index)=“my value”

[Prakash_Raj]

Is this work on for each row???
i’m also working on this type of task and i want to get the index of the row for consecutive cells…
Thanks in advance.

[ClaytonM]

Hi @Prakash_Raj
Yeah, so you can get the index using the row object.

For each row In dt1
    rIndex = dt1.Rows.IndexOf(row)

This same thing can be done with other arrays as well.

An example use-case for this might be if you would like to continue with the last processed row item, so you would store the index then skip all the row items to that index.
—It would look like this:

For each row In dt1.AsEnumerable.Skip(rIndex) //with DataRow as Type Argument
    rIndex = dt1.Rows.IndexOf(row)

In that use-case, if an error was to occur and exit out of the loop, when a retry attempt happens, it will skip to the last index that was being processed at the time of the error.

I hope this info is helpful.

Regards.

[Prakash_Raj]

I am used this method it will get the first cell index. how can i get the row index of consecutive cells any samples available.

[ClaytonM]

Hi.

I don’t understand what you want to do. Can you provide some screenshots or a better explanation?

Just to rehash what I posted previously…
You can get the index of consecutive rows by using a For each.
For example,

For each row in dt1
    Assign index = dt1.Rows.IndexOf(row)

And, that will return set the index for each row in the table.

EDIT: also, that example is pseudocode so you can replace each line with its corresponding activity.

Regards.

[Prakash_Raj]

This is the excel sheet. In this sheet i’m extract as a data table and process through for each row in get row item to iterate row by row and finally i want to write a message(column) in excel one by one when get row iteration. How can i do this process.

[ClaytonM]

Ok so there are 2 approaches that I will touch on. 1) assign the message to the data table, then use Write Range to update file, or 2) use Write Cell with the row index of the iteration

For first approach, it would look something like this:

Read Range to dt1
For each row in dt1
    <process iteration>
    Assign activity: row("Message") = "message"

Write Range // using dt1

Using the Assign activity like this let’s you update the data table using the Column name or index as you loop through each row. You can then use Write Range back to the file either inside the loop or after the loop.

Second approach would look like this:

Read Range to dt1
For each row in dt1
    Assign activity: rIndex = dt1.Rows.IndexOf(row)
    Assign activity: colAlpha = Convert.ToChar(dt1.Columns.IndexOf("Message")+65)
    <process iteration>
    Write Cell // using range: colAlpha+rIndex.ToString

So in this approach, you would need the row number and probably the column to be used for the range in the Write Cell.

Apologies if I make any mistakes, but hope this helps answer your question.

Regards.

[Prakash_Raj]

it works thanks for you reply…

[shilpapmk (shilpa)]

Could you pls share the xaml file for the above im trying to find empty cells for each row and fetch the column name or index

[Panaj.Abh (Panaj Abhavudhichai)]

Hi,
You can use Lambda expressions instead of for each loop to find a cell position of your word
even you does not know which column contain that word.

Firstly,
read and cast datatable to datacolumn
dtcol=dt.Columns.Cast(Of DataColumn)().ToArray

then use nested Lambda expressions to find the row index
RowIndex=dt.Rows.IndexOf(dt_Temp.AsEnumerable().Where(Function(row) dtcol.AsEnumerable().Any(Function(col) row(col).ToString.Trim=KeywordToFind.Trim)).FirstOrDefault)
and then column index
ColIndex=dt.Columns.IndexOf(dtcol.AsEnumerable().Where(Function(col) dt(RowIndex)(col).ToString.Trim=KeywordToFind.Trim).FirstOrDefault)

Lastly, convert column index to char and concat to row index.

I can’t upload .xaml to this forum because i’m new user
so i decide to upload xaml file here:
https://drive.google.com/file/d/1M9shKV9h7y96zFTmRudBt_ciq71Sds1D/

Sorry for my English.
Regard,
PanajA