How to copy a formula to every other column

[Pandrade]

Hi,
How can I copy a formula with more than a thousand columns to every other column in Excel?

 

[Anthony47]

Now I don't know if we are talking of a new problem or the initial one, with different details...

Let me go with this different proposal:
1) Insert this macro in a Standard Module of your vba project:

Sub PanCopy()
Dim LastC As Long, LastR As Long
Dim tStartR As Long, tStartC As Long
'
'
LastC = Cells.Find(What:="*", After:=[A1], _
              SearchOrder:=xlByColumns, _
              SearchDirection:=xlPrevious).Column
On Error GoTo 0
'
tStartC = Selection.Cells(1, 1).Column
tStartR = Selection.Cells(1, 1).row
Selection.Offset(, 1).Resize(, LastC).ClearContents

Selection.Copy
For I = tStartC + 2 To LastC Step 2
    Cells(tStartR, I).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
Next I
Application.CutCopyMode = False
End Sub

2) you write the formulas that do the job on the first column of data; from what I see in the nanopicture, you need formulas in L48-L49-L50. Please don't use "absolute addressing" (fo example: $L$2:$L$46) but only "relative addressing" (like L2:L46).
3) when you are ok, select ALL the formula that you wish to replicate to the right
4) select ALL the formula you wish to repeat to the right then run this new Sub PanCopy

Your formula will be copied to the right till the last column, starting from column "selection+2" and stepping by 2

Bye

 

[Pandrade]

Anthony47 I really appreciate your assistance. The first code worked for count, but in addition I need to sum, and then get the average. Again thank you for taking the time.

 

[Anthony47]

Was the approach described in post #21 useful? I mean: you set the formulas in the first column and the macro copy them

 

[Pandrade]

Anthony47,

Approach described in post #21 almost works as needed except it doesn't account for columns of different lengths. How can that be addressed. I get the sums where they are supposed to be but on columns that are longer I get the little green arrow in the upper left corner of the cell because it doesn't sum all the quantities for those longer columns.

 

[Pandrade]

That's because I'm using the sum function on the first salary column, so it only sums the range of the first column. The second formula I will use your VBA to copy will be average. That one should work fine but for the sum it needs something else.

 

[Pandrade]

It only sums the range of the first column when sum function is copied to the rest of the salary columns.

 

[Anthony47]

It only sums the range of the first column when sum function is copied to the rest of the salary columns.

If the formula, when copied, return always the sum of the initial column then it means you used "absolute addresses" in its argument; for example =SUM($L$2:$L$47)
You must use "relative addresses", for example L2:L47

If you don't follow me then tel us which formulas you set and in which position and we'll try to modify them

Bye

 

[Pandrade]

Anthony47,

Thank you again. The way I compensated was I increased the range for the first column when entering the original formula, since the rest of the cells are empty it doesn't affect the result for the columns with smaller ranges but it does make it so that the columns with longer ranges will include all cells. I will try it again, this time by clicking the relative addresses button and see if that works.

 

[Anthony47]

What is "the relative address button"?
I talked about your formulas...