VBA Autofill Two Columns with unique Formula in each Column

%3CLINGO-SUB%20id%3D%22lingo-sub-2896953%22%20slang%3D%22en-US%22%3EVBA%20Autofill%20Two%20Columns%20with%20unique%20Formula%20in%20each%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2896953%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20have%20a%20report%20we%20want%20to%20pull%2C%20insert%20two%20columns%2C%20put%202%20different%20formulas%20in%20row%202%20of%20each%20column%2C%20then%20hide%20the%20referenced%208%20columns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20we%20want%20to%20auto%20fill%20to%20the%20last%20row%20with%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELine%2023%20has%20the%20error.%20Trying%20to%20combine%20into%201%20row%20by%20saying%20%3DRange(%22P2%3AQ%22%20will%20autofill%20a%20shifted%20formula%20from%20column%20P%20into%20Column%20Q%20and%20not%20fill%20P%20at%20all.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20DistributionCheck()%0A'%0A'%20Macro2%20Macro%0A'%0A%0A'%0A%20%20%20%20Columns(%22P%3AQ%22).Select%0A%20%20%20%20Selection.Insert%20Shift%3A%3DxlToRight%2C%20CopyOrigin%3A%3DxlFormatFromLeftOrAbove%0A%20%20%20%20Range(%22P1%22).Select%0A%20%20%20%20ActiveCell.FormulaR1C1%20%3D%20%22Canceled%22%0A%20%20%20%20Range(%22Q1%22).Select%0A%20%20%20%20ActiveCell.FormulaR1C1%20%3D%20%22Inquiry%20Only%22%0A%20%20%20%20Range(%22P2%22).Select%0A%20%20%20%20%0A%20%20%20%20Range(%22P2%22).Formula%20%3D%20%22%3DIF(R2%3DTRUE%2CTRUE%2CIF(S2%3DTRUE%2CTRUE%2CIF(T2%3DTRUE%2CTRUE%2CIF(U2%3DTRUE%2CTRUE%2CFALSE))))%22%0A%20%20%20%20Range(%22Q2%22).Formula%20%3D%20%22%3DIF(V2%3DTRUE%2CTRUE%2CIF(W2%3DTRUE%2CTRUE%2CIF(X2%3DTRUE%2CTRUE%2CIF(Y2%3DTRUE%2CTRUE%2CFALSE))))%22%0A%20%20%20%20%0A%20%20%20%20Range(%22R%3AY%22).EntireColumn.Hidden%20%3D%20True%0A%20%20%20%20%0A%20%20%20%20Selection.AutoFill%20Destination%3A%3DRange(%22P2%3AP%22%20%26amp%3B%20Range(%22B%22%20%26amp%3B%20Rows.Count).End(xlUp).Row)%0A%20%20%20%20Range(Selection%2C%20Selection.End(xlDown)).Select%0A%0A%20%20%20%20Selection.AutoFill%20Destination%3A%3DRange(%22Q2%3AQ%22%20%26amp%3B%20Range(%22B%22%20%26amp%3B%20Rows.Count).End(xlUp).Row)%0A%20%20%20%20Range(Selection%2C%20Selection.End(xlDown)).Select%0A%20%20%20%20%0A%20%20%20%20%0A%20%20%20%20%0A%20%20%20%20%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2896953%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

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

 

 

 

1 Reply
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?