Forum Discussion

Andrew_Wyckoff's avatar
Andrew_Wyckoff
Copper Contributor
Oct 28, 2021

VBA Autofill Two Columns with unique Formula in each Column

We have a report we want to pull, insert two columns, put 2 different formulas in row 2 of each column, then hide the referenced 8 columns.

 

Then we want to auto fill to the last row with value.

 

Line 23 has the error. Trying to combine into 1 row by saying =Range("P2:Q" will autofill a shifted formula from column P into Column Q and not fill P at all.

 

 

Sub DistributionCheck()
'
' Macro2 Macro
'

'
    Columns("P:Q").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "Canceled"
    Range("Q1").Select
    ActiveCell.FormulaR1C1 = "Inquiry Only"
    Range("P2").Select
    
    Range("P2").Formula = "=IF(R2=TRUE,TRUE,IF(S2=TRUE,TRUE,IF(T2=TRUE,TRUE,IF(U2=TRUE,TRUE,FALSE))))"
    Range("Q2").Formula = "=IF(V2=TRUE,TRUE,IF(W2=TRUE,TRUE,IF(X2=TRUE,TRUE,IF(Y2=TRUE,TRUE,FALSE))))"
    
    Range("R:Y").EntireColumn.Hidden = True
    
    Selection.AutoFill Destination:=Range("P2:P" & Range("B" & Rows.Count).End(xlUp).Row)
    Range(Selection, Selection.End(xlDown)).Select

    Selection.AutoFill Destination:=Range("Q2:Q" & Range("B" & Rows.Count).End(xlUp).Row)
    Range(Selection, Selection.End(xlDown)).Select
    
    
    
    
End Sub

 

 

 

excel
Formulas and Functions
Macros and VBA

1 Reply

  • mathetes's avatar
    mathetes
    Gold Contributor
    Oct 29, 2021
    Well, as of this writing, you have 66 views and 0 replies. There's a reason, I suspect: what you describe is not all that clear. What you provide us with is a macro that is (presumably) trying to do what you describe, but NOT the spreadsheet in which all this is to take place. My guess is that nobody can really offer any specific advice without seeing the actual spreadsheet OR, at the very least, an accurate mockup.

    Could you provide one or the other?

Resources