Howto create a formula in VBA based on another formula

[brianfosterblack]

I am running a macro in VBA which ends in a cell in row 1 of the worksheet. I then want to create the formula in the Activecell, in my macro to increment the row number of the Cell 2 cells to the left by 1 in the Active cell. The formula 2 columns to the left will be in the same row number 1 of the worksheet.
eg. if the formula 2 cells to the left =

='1Master'!$AA2

The formula I want to create will increment the above formula by 1 row so I end up with the activecell formula as

='1Master'!$AA3

If I run the Macro again, I will finish in the cell two columns to the right and will then create a formula again

='1Master'!$AA4

Can anyone help with this code.

 

[Joe4]

Can you walk us through a few cycles of an actual example in more detail?
Where exactly is this formula being entered (what cell)?
Is it overwriting a current formula, or adding a new formula in a blank cell?
How does it know what to use for the formula the first time through?

 

[brianfosterblack]

Can you walk us through a few cycles of an actual example in more detail?
Where exactly is this formula being entered (what cell)?
Is it overwriting a current formula, or adding a new formula in a blank cell?
How does it know what to use for the formula the first time through?

Hi Joe, thanks for the reply.
The first formula is already inserted in the worksheet in cell C1. I currently have 2 columns in column C and D and the formula is in row 1 of column C
Every time I get a new client, I have to add 2 columns to the summary worksheet.
To do this I have a macro which copies the last 2 columns in the summary worksheet and in the next 2 available empty columns it pastes the formulae, formats and column widths for the new client.. At the top of the first column is a formula referring to the list of all the client sheets in my workbook. In Cell C1 the formula refers to

=1Master!AA2

When I copy the columns I one again end up with a formula which also =

=1Master!AA2

but as I want it to refer to the next name in the sheet list I need the formula to be changed to

=1Master!AA3

The formulae in the columns use the "Indirect" function with the the above formula to pick up the data in the relevant client sheet referred to in this formula.

Hope this makes sense.

 

[Joe4]

Still hoping more the nitty-gritty details here (without any kind of images, all we have to work off of is your explanation).

So, if I understand you correctly, you are starting off with this formula in cell C1:

=1Master!AA2

is that correct?

And then what cell exactly (cell address, please) arr you trying to copy this formula to?
And you want that to be:

=1Master!AA3

correct?

The formulae in the columns use the "Indirect" function with the the above formula to pick up the data in the relevant client sheet referred to in this formula.

I am not sure where this plays in to everything.
What exactly is the formula, and where is that located?

 

[brianfosterblack]

Still hoping more the nitty-gritty details here (without any kind of images, all we have to work off of is your explanation).

So, if I understand you correctly, you are starting off with this formula in cell C1:

=1Master!AA2

is that correct?

And then what cell exactly (cell address, please) arr you trying to copy this formula to?
And you want that to be:

=1Master!AA3

correct?


I am not sure where this plays in to everything.
What exactly is the formula, and where is that located?

Lets take this with your first Formula in C1 which is correct is
when I copy the last 2 columns, which in this case is column C and D to the first empty columns it copies to columns E and F with the formula in C1 now being copied to Column E1 as

=Master!AA2

but the formula in Column E1 needs to be

=Master!AA3

The next time I run the Macro I will copy columns E & F to columns G and H and in this case the formula copied to cell G1 will need to be changed to

=Master!AA4

 

[Joe4]

If you put this formula in cell C1, I think it will do what you want when you copy it to E1, G1, I1, etc.

=INDIRECT("'1Master'!AA" & (COLUMN()+1)/2)

It will increase the row reference by 1 each time, even though you are jumping 2 columns.

 

[brianfosterblack]

If you put this formula in cell C1, I think it will do what you want when you copy it to E1, G1, I1, etc.

=INDIRECT("'1Master'!AA" & (COLUMN()+1)/2)

It will increase the row reference by 1 each time, even though you are jumping 2 columns.

You are right - this works perfectly, thank you for the patience and effort.

 

[Joe4]

You are welcome!
Glad I was able to help.

Note: Here is another solution that uses OFFSET instead of INDIRECT.
I don't think there is any advantage to this one, just another option:

=OFFSET('1Master'!$AA$1,(COLUMN()-1)/2,0)