VBA that edits itself when run

[brayleyboy]

I have a general question.

Is it possible to write a macro with a bit at the end that edits and saves the macro.

A simple example might be a piece of code that selects a cell (A1), inputs a value, then edits the code so that the next time it is run it selects another Cell (A2) etc.

Many thanks

 

[onlyadrafter]

Hello,

FOr your example you won't need to edit the code you will need a FOR NEXT loop.

Can you be more specific?

 

[brayleyboy]

OK, sorry if this gets a bit complicated!

I have a macro which basicaly populates a couple of cells by referencing them to cells in another sheet (see below)

    Range("L12").Select
    ActiveCell.FormulaR1C1 = "='CS Belfast'!R[-4]C"
    Range("M12").Select
    ActiveCell.FormulaR1C1 = "='CS Belfast'!R[-4]C"

When the macro is next run (i.e. a week later) I need it to reference the next cell down (i.e. 'CS Belfast'!R[-5]C')

I have tried to simplify the example. Essentially what I want is a piece of code to go at the end that edits the code and saves the macro for use at a later date. Each week i need the same macro to reference a cell 1 down from the previous week.

If you could help that would be great.

 

[onlyadrafter]

Hello,

Why not just use a cell value?

i.e. in O1 enter 4 and use

Range("L12").FormulaR1C1 = "=R[-" & Range("O1").Value & "]C"
Range("M12").FormulaR1C1 = "=R[-" & Range("O1").Value & "]C"
Range("O1").Value = Range("O1").Value + 1