VBA Code For shifting of formula to next Column

[leo73pk]

I'm working on a file from which a sample data & structure is enclosed for a reference.

Currently for the month of April-2020, Cell ranges F5:F8 and F12:F15 are linked with corresponding cells of Column E. On first day of every month, when I Insert word "DONE" in cell E1, the Cell ranges F5:F8 and F12:F15 formula turns into absolute value and once Cell E1 is blank the Cell range F5:F8 and F12:F15 are again linked to Corresponding Cells of Column E. I'm doing this thru the following VBA Code.

Please Login or Register  to view this content.

Now I want that on 1st day June when I insert word DONE in Cell E1, Values of April-2020 Cell ranges F5:F8, F12:F15 and May-2020 Cell ranges G5:G8, G12:G15 turns into absolute value and Next Column's (June-2020) cell ranges H5:H8 and H12:H15 are linked with corresponding cells of Column E similarly it goes till Column T (June-2021).

In view of above, kindly suggest a VBA Code or modify the above referred VBA Code.

[Eastw00d]

Hi, I have following suggestion for you
Change the value in E1 into the month AFTER the month that is done, so in your case if April (1) is done, put 2 in E1 (and 3 if May is done etc.). (as it is in your row of current months)
then I can provide you this code:

Please Login or Register  to view this content.

I deliberately put both ranges in different blocks of coding for better understanding.
Cheers
Erwin

[leo73pk]

Dear Eastwood,

First I would like to thank you for your time to review and propose a valuable solution. Moreover, I would like to ask that the code works for 15 months. Lets say for next 15 months if I place the 1st month i.e. 16th month, again in Column F how the Column F automatically linked to the corresponding cells of Column E.

[Eastw00d]

Yes, now I have amended the ELSE part of the second IF:
if you input 1 in cell E1, the following happens:
The whole range is being emptied, watch out, it is NOT saved, the formula is set back in column F.

Please Login or Register  to view this content.

and then you can start over again.

Cheers
Erwin

[leo73pk]

Dear Eastwood,

Thanks for your time and efforts to help me. I'm enclosing a sample workbook wherein I have incorporated your proposed VBA Code and my findings are as follows;

1. Columns F5:F8 are linked with Cells E5:E8, whereas E5:E8 are linked with Sheet1 Corresponding Cells. When I change value of Cell E1=2, it turns the values of Cells F5:F8 into absolute numbers (as required).
2. It also shifts the formula to Next Column cells G5:G8 (Link with the Corresponding cells of Column E) (as required).
3. It also clear the entire range from Column F:Column T and again Link Column F with Corresponding Cells of Column E. (as required)
4. But with the Change in values of Column E1, It also turns the values of Column E to Zero while shifting the formula to next column (not required) because Column E is linked with sheet1.Column E and this link should be remain intact at all time.
5. I want to apply this VBA to extended ranges as as test case i tried to modify it but doesn't work for Ranges F5:F16 and F21:F50 in the sample workbook.
6. if you guide me to modify the VBA code for F5:F16 and F21:F50, i'll apply it to other ranges at my own

Regards,

[Eastw00d]

Hi Leo,
Thank you for responding, I am not sure I understand you point 4 correctly:
So if you don't want E5 and below to be reset to 0, delete or Rem this line: Cells(i, 5) = 0

and (point 5&6) for to work your code in the last workbook, change the following in the code:
8 --> 16, 12 --> 21, 15 --> 50 (but not in this line: If [E1] > 1 And [E1] <= 15 Then )

But I can make life easier for you: you only have to change R1 and R2 in following code when the Ranges are changing.

Please Login or Register  to view this content.

Cheers
Erwin

[leo73pk]

Dear Eastwood,

I appreciate your time and efforts to promptly responding to my queries. The recent code proposed by you is working as per requirement.
However, when I tried to modify the vba code to add a New Range "54:84" by making following changes;

Dim R1$, R2$, R3$
ReDim rn(1)
Dim i%, R$

R1 = "5:16" '<--- to change
R2 = "21:50" '<--- to change
R3 = "54:84"
rn(0) = Split(R1, ":")
rn(1) = Split(R2, ":")
rn(2) = Split(R3, ":")

and save the code its gives runtime error 9.

Do i need to amend the other areas of vba code as well if i want to add more ranges. (54:84, 87:101, 105:115, 120:124 and so forth) Please advise.

[Eastw00d]

If you would have said that you had more ranges, I would have adapted the code immidiately in another way. Give me a little time to think about this.
I need to rebuild the code.
Cheers
Erwin

[leo73pk]

My apologies for not elaborating it precisely. I'll be grateful if you also highlight which part to be modified in case I have to add more ranges in future.

[Eastw00d]

This should do it:

Please Login or Register  to view this content.

Cheers
Erwin

[leo73pk]

Dear Erwin,

Thank you so much for your time and efforts to help me. Your proposed VBA works as per requirement really grateful.

Best Regards,