Duplicating Formulas using R1C1 format problem

[aquixano]

I’m working on a file with a range name dynamically updated via VBA and saved in a named range cell such as cell A1 (named “test”) in the attached workbook. The actual destination range will be four columns wide and 20-50 rows high. In this range I want to duplicate a set of formulas in a four cell named range on the same worksheet (this test file uses a two cell template – name “testfomula1”). I had success doing this before with a single cell template, or a multi-cell template on a single destination row. But I’ve never tried a multi-cell template with a multi-row destination range.

The following line of code identifies the correct range and copies the template row into all cells in the area, but for some reason I cannot discover, it increment the row references by 2 – i.e. the row references all are odd numbers 1,3,5 through 39 vs. 1,2,3 through20. The Test button runs the macro.

Please Login or Register  to view this content.

Anyone have any idea why this is occurring / how to correct it? Thanks for reading & any help.

[nilem]

Hi aquixano,
maybe so

Please Login or Register  to view this content.

[aquixano]

Thanks Nilem,
You solution solves my problem, but the code’s behavior still bothered me – still haven’t found a reason why it’s incrementing the R1C1 formula but I have discovered something else. I used the R1C1 syntax because I wanted relative references. While I couldn’t find explanations for all the .formula variations, through trial and error I discovered if I modify the template row formulas to use the cell references of the first target row with .formula (vs. formualR1c1), the following code works as desired,

Please Login or Register  to view this content.

I don’t know why it increments properly since I thought .formula created absolute references, but I guess that’s a mystery for another day. Thanks for your help. Your suggestion got me thinking on a new path. Thanks again.