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]

I suggest that you explain "what" you wish to do (for exampe "Summarize on row 1 of each used column how many cells are populated in that column") and not "how" you think that should be done (for example "Copy a formula that Sums the used cells in a column to each of the other used columns")

Bye

 

[Pandrade]

I have thousands of columns that alternate between salary and quantity. I need to have the count formula at the end of each quantity column count the cells above that have a number in them. The difficulty is copying the formula to every other column, on the quantity columns. The columns are different lengths so there will be some empty rows between the last cell used and the count formula.

 

[Fluff]

Cross posted How to copy a formula to every other column

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Anthony47]

May be:

Sub PanTotal()
Dim LastC As Long, LastR As Long, SumR As Long, tStart As String
Dim tStartR As Long, tStartC As Long
'
tStart = "B4"           '<<< The TopLeft Address of the Table with Salary/Quantity
SumR = 0                '<<< The Row that will be used fot the Total; if ZERO means "at the bottom"
'
LastC = Cells.Find(What:="*", After:=[A1], _
              SearchOrder:=xlByColumns, _
              SearchDirection:=xlPrevious).Column
LastR = Cells.Find(What:="*", After:=[A1], _
              SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious).row
On Error GoTo 0

If SumR = 0 Then SumR = LastR + 2
tStartR = Range(tStart).Cells(1, 1).row
tStartC = Range(tStart).Cells(1, 1).Column
Cells(SumR, tStartC).Resize(1, LastC).ClearContents
For I = tStartC To LastC Step 2
    Cells(SumR, I + 1).Value = Application.WorksheetFunction.CountA(Cells(tStartR, I + 1).Resize(LastR, 1))
Next I
End Sub

Copy the code in a "Standard module" of your vba; the two lines marked <<< need to be adapted to your scenario

The the macro will put at the bottom of your table the count of how many cells are in each even column (i.e. start from the Second, then increment by two)

Bye

 

[Pandrade]

Anthony47 Thank you that worked!

 

[Pandrade]

That code worked for counting cells in every other column with data, but I have about 4 more calculations that I need to perform on every other column across thousands of columns. Is there any way to copy and pate a calculation formula across the columns, in an every other column manner?

 

[COwen]

Do you already have the formula? If so can you post it?

 

[HappierThan]

G'day *Pandrade, Had to think about a different approach; start with Inserting a new row beneath your headers so you would then have a new row 2.

In cell A2 =SUBTOTAL(3,A3:A10000) then select and fill as far right as you need. Increase the number of rows as necessary. The first number 3 is the COUNTA function which would give you a count of the number of rows that have data in each column. Cheers H

 

[Pandrade]

Anthony47 Here is what I need to do:
I am working on a worksheet with thousands of columns. The columns alternate between salary and quantity. I need to place the sum total for each salary column on row 51 and the average for each salary column will be calculated by dividing the sum of each salary by the number of salaries found on row 50, for each salary column (they start at column L). The average will be placed on row 52 for each salary column (every other column). Here is a picture for clarity:

 

[Pandrade]

Hello Fluff,

I posted a new thread for this question because it is different. The first question was how to count every other column, now I need to sum and average. I was instructed previously to start a new thread if it is a different question.