Place formulas in every other column?

[Madeiracakez]

Trying to clean up a spreadsheet for work, we have thousands of tasks, each with dates that correspondences came in and went out, with the NETWORKDAYS formula between each, to show days between these exchanges. Is there a quick way of placing this formula over every other column for the entire workbook so that there are still columns in between for the dates to be filled?

 

[Joe4]

Welcome to the Board!

Probably the best way is to use VBA. We could help you come up with code, but obviously need a lot more detail from you (i.e. what cells we are talking about, what formulas to insert, etc).

 

[Madeiracakez]

Thank you, I definitely fudged my excel skills on my CV and am regretting it now! the in date is in cell W2, then the formula is in X2, the out date is then in Y2, this pattern continues all the way to the right and down throughout the spreadsheet. So I need the formula to be between the in and the out date and to continue this way, the whole sheet has been a mess since I started and so there are more than 1000 rows of different tasks all with different in and out dates. I don't think a variation on NETWORKDAYS to exclude weekends is necessary.

 

[Joe4]

What exactly does your formula look like?

Are the formulas in column X and Z the same?
If not, please post each formula.

What is the last column you need populated?

 

[Madeiracakez]

The formulas in X and Y are the same,
=IF(Y2>0,NETWORKDAYS(W2,Y2,2),0)
Obviously as it carries over, it changes reference point.

 

[Madeiracakez]

The last column I guess would be DP

 

[Joe4]

The formulas in X and Y are the same,
=IF(Y2>0,NETWORKDAYS(W2,Y2,2),0)
Obviously as it carries over, it changes reference point.

Hmmm... that does not sound right to me.
Did you mean to say X and Z, not Z and Y?

And based on your example, the formulas in X and Z cannot be the same thing, as they are returning different values!

 

[Madeiracakez]

I did mean X and Z, sorry,
=IF(AA2>0,NETWORKDAYS(Y2,AA2,2),0)
this is in Z

 

[Joe4]

Try this:

Sub MyFormulaMacro()

    Dim lr As Long
    Dim c As Long
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column W
    lr = Cells(Rows.Count, "W").End(xlUp).Row
    
'   Populate every other column from X to DP
    For c = 24 To 120 Step 2
'       Populate all rows in column with formula
        Range(Cells(2, c), Cells(lr, c)).FormulaR1C1 = "=IF(RC[1]>0,NETWORKDAYS(RC[-1],RC[1],2),0)"
    Next c
    
    Application.ScreenUpdating = True
    
End Sub

 

[Madeiracakez]

That has worked perfectly, thank you so much!