Help needed on R1C1 formula reference

Hi all,

I run a macro like like this on screenshot attached below:

Please Login or Register  to view this content.

but the column reference for the SUM part became XED and XEE.
Capture.PNG

I then autofilled it across the row, and in the immediate next column the column reference of the first reference in became C[0] (instead of C[29]), as shown in attachment below.

Capture2.PNG

Essentially what I am trying to do is, if the date is less than 30 days from the start date, sum cell-to-the-left and cell-below, if date is larger than 30 days then sum cell-below and the 29 cells to the left of it. Then autofill the row until a certain point (I have sorted the autofill bit out). I imagine this shouldn't be difficult but somehow I just can't make it work with a macro.

Any help much appreciated, please let me know if there is further information I need to provide. Thank you very much in advance.

Please attach a workbook - screenshots are no good to work from.

Please make clear what your required outcome is - enter the right formula manually, if necessary, so we can see what we're trying to achieve with code, and in which range.

Hello OllyXLS,

Thank you very much for the response, I have attached a test workbook below. The blank void in the middle is normally filled with data but I have removed them since it's irrelevant to the macro concerned and involves sensitive business data.

TestWorkbook1.xls

Information:
Orange rows = variable values drawn from another source

Row 27 and row 29 are the desired outcome of the macro, except D27 and D29.

Cell D27 and D29 (highlighted in yellow): I ran macro on these two cells, resulting in the undesired referencing

Row 32 is a dummy row I put in to illustrate when I want the formula in row 27 and 29 to change.


Hope these help, please let me know if there is any more information I could provide with. Thank you very much for your time and patience.

EDIT:
What I am trying to do is: a macro that can populate cell D27 and D29 with an IF formula that I can autofill across the row till the last cell with data in row 1--I have got the autofill part sorted. At the moment if I drag formula in D27 (generated from macro) across it will give REF! when going into the 31st day.

try:

Please Login or Register  to view this content.