How to select the next cell to the right in loop

[Raja10]

I am trying to use a formula to select a date which is in cell "A1" and paste it in cell "K2". the take the value from "K2" add 7 days and put that value in "L2", then add +7 days to "L2" and put the value in "M2" and so on for the next 100 columns. is it possible to use Range(" ").select instead of cell(x,y) in offset formula?
Any help is appreciated. Thank you!

[Arkadi]

Hi Raja10,

How about this:

Please Login or Register  to view this content.

If you don't want the formulas to remain just add ws.Range("L2").Resize(, 99).Value = ws.Range("L2").Resize(, 99).Value

[dangelor]

Without formulas...

Please Login or Register  to view this content.

[Raja10]

It worked. Thank you!

can you explain [ws.Range("L2").Resize(, 99).Formula = "=DATE(YEAR(K2),MONTH(K2),DAY(K2)+7)"]
If possible can you also tell me how to use this in loop conditions?

[Arkadi]

That line of code takes L2, resizes it 0 rows down and 99 across (for a total of 100)
Then it applies a formula that takes the year of K2, month of K2, and day of K2 +7 days. However, when you set the formula like that, it works just like dragging across, and each cell to the right is taking the year date and day info from the cell to the left of it.

[Arkadi]

Oh and as far as the loop goes, I don't see why you'd want to loop 100 times instead of filling all cells in one shot, but you wouldn't use the formula approach then, you'd want dangelor's approach where vba just does the calculation. However like I posted, you can just add

Please Login or Register  to view this content.

to convert the formulas into static values, and that is in theory faster than a loop.

[Raja10]

this worked but the format is in number. how to change the format to (month/day) for example "may-27"?

[dangelor]

Try...

Please Login or Register  to view this content.

[Arkadi]

Please Login or Register  to view this content.