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

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!

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

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

Hi ,

PFB

Something like this

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!

It should be:

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

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!

Yes, exactly!

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!

Try this instead:

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

Best,
Charbel

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

Okay update:

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