Read range and derive write range's row from a cell value

Help StudioX
, ,
1

Hi All,

I am trying to copy and paste as values for those cells with BBG formula.(Currently shown as #NAME? on the screenshot as we have yet to extract the value).

Once we refresh the excel, row 43 will be reflected as number values instead of #NAME?. However, it will still be a formula instead of value. Hence, Im using write and copy range activity to paste it as values.
It works but however, I have multiple sheets to work on and for every month, the row changes (next month it will be 44 instead of 43). It will not be feasible to change the range every month.

Is there a way the for the row value in write range activity to be derived from a cell?
Meaning, I can create a formula on cell A100 that returns “44”, as row 44 is the first row that gives me error.
Can the write range activity read cell A100 and start writing the copied data from row 44?

And for the next month, cell A100 = 45 and the write range activity read this cell and start writing the copied data from row 45.

Is this possible? Thanks!

image
image764×195 4.42 KB

2

Yes, It is possible.

  1. Read cell and take output in string varible lets say startRange
  2. Create another varible for range ex rangeExcel
  3. Concatenate this with rangeExcel+startRange
3

Thanks Lakshay.
Apologies can i have an example of this.

For example i have to read cell A100 and define the variable as string and name it startRange.

After that, I create another variable (as string too?) and name it as rangeExcel.

For 3rd point, can i understand what is meant by concentrate it?

For my write range below, do i type rangeExcel+startRange in starting cell or?

Thanks!!

image

4

Hi ,

PFB

image
image1096×327 10.5 KB

image
image1156×545 22.1 KB

Something like this

5

Apologies for the delayed response @Lakshay_Verma .
See that the above worked, however I need some clarifications.

To refresh, I am trying to start the read range from a derived number in a certain cell.
For example, cell A100 is number 98 and I want to start reading my range from B98:J98.
I have created a variable for A100 and named it StartRange.

In my read range, how do I state the range? I tried “B”+StartRange:“J”+StartRange but it does not work.

I am able to read cell by stating “J”+StartRange but when i tried read range, it does not work with the above.

Thanks!

6

It should be:

"B" + StartRange + ":J" + StartRange
7

Thank you very much @Charbel1 !
Can I ask one more question. Is it possible to read range for all rows before my StartRange?
For example, I defined my StartRange in Cell A100 = value 98.
I want to read range for all rows from B1 to J97.
How do I state the range in this case?

Will it be “B1” + “:J” + StartRange - 1?

Thanks!

8

Yes, exactly!

9

Hi @Charbel1 , I tried that formula but got the below error.
image

I have to define the StartRange variable as generic value if not it will give me error. But when I typed in “B5” + “:J” + StartRange - 1 for read range, it is telling me that I cannot subtract.

Is this expected? I have my full workflow below! Thanks!

image
image458×552 33.7 KB

image
image1390×79 16.2 KB

10

Try this instead:

"B5" + ":J" + CInt(StartRange) - 1

Best,
Charbel

11

Hi @Charbel1 , I’m getting an error of "Option Strict on disallows implicit conversions from ‘String’ to ‘Double’ " if I added the CInt.

Thanks

1 Like
12

Okay update:

"B5" + ":J" + (CInt(StartRange) - 1).ToString
13
  1. Insert a read range workflow
  2. Add a for each row workflow
  3. Start populating whatever thats needed in those activities to read you file properly