Problem with read range activity

Help
1

Can someone please help me facing problem with read range I have an excel I need to read data from column C through K that start by row 8 but I don’t know the exact where row ends i have to read until the above row which contains string as “ total net amount “ and total amount row will anything like row 33,55,… am sharing file

image
image2851×2138 2.81 MB
.after reading data whatever data row 8 contains which will be my headers by using those I need to read the data row by row which header contains “invoice” and “Sap cash discount “ how to achieve this can some one pls help me out

2

Hi @Deepika

Use Read Range and set the range as"C3:K100"
use output datatable activity
Check the datatable rows.count
based on check the rowwise calculation and do the calculations

Thanks
AshwinS

3

My data start by row 8 and need to read from column C through K but I don’t know where it ends only clue which I have I need to check which row contains “Total Net Amonut”. If i find this the above row of total net amount will be my end row.

4

Hi @Deepika
Check the range as C8:K+DtRows.count.tostring

Thanks
Ashwin S

5

It’s not working here any assistance please how to achieve this

6

Hi @Deepika

Show the error screenshot

Thanks
Ashwin S

7

It’s giving different errors can u explain me the steps how I can achieve the above scenario

8

Hi @Deepika

Firstly, read the excel sheet from C8 without defining the limits and save as a datatable.
Use For Each row on data table and apply condition in if block that is row(“Cash Deposit”).tostring.contains(“Total Net Amount”)
If that condition met then get the index of that row and save in a variable.
Again read the excel sheet and pass range as (C8:K+Variable).

9

Hi @Deepika,

For this i’ll suggest you to use LookUp Range activity.
Follow the steps below.

  1. Take LookUp Range activity (Output variable —> loc)
    specify the sheet name, range —> “” and value —> “Total Difference Amt”
    by this it’ll give us the location of cell containing text “Total Difference Amt”

  2. but, we don’t want that row in range. (till previous row)
    loc = loc.Split("K"C)(1) —> this will give you digits

  3. Read Range —> “C8:K”+(CInt(loc)-1).ToString

10

image
image2851×2138 2.31 MB
am getting this error
image
image2851×2138 2.3 MB

11

When am passing range as only C8 it’s giving error range cannot be classified

12

Can u tell me when I used look up range I got output cell as range like “k22” but now I need to subtract two cells or rows i. e “k22-2” so that I get output as k20 after subtracting this going to be my end cell how to achieve this I mean subtracting of cells ! Thanks

13

Hey @Deepika,

See once you have Cell = K22 and you want K20, for this

  1. Assign
    Cell = Cell.Split("K"C)(1) —> spliting string K22 with K.
    —> by this you’ll get 22 as at string.

  2. Take Read Range
    Range should be —> “C8:K”+(CInt(Cell)-2).ToString

(CInt(Cell)-2).ToString - by this Cell be converted into int variable and subtract 2 from cell, which is 22-2 = 20, because we can’t subtract 2 from string variable.
Now 20 which is int be converted into string using .ToString and Concatenated to “C8:K” and range will become —> “C8:K20”

:slight_smile: