Name a column

[dgrimm]

OK, I know I have done this in the past but I am having a major brain fart or something. I am trying to name columns so that I can just put the name in a formula to make sure that I am selecting the correct columns. My code is as follows and I am getting a Type Mismatch error on the line with the formula that contains countifs...

Sub All_All()
Dim lRow As Long
Dim FCode As String
Dim DCode As String
Dim SCode As String
Dim PCode As Range
Dim Shift As Range
Dim TOI2 As Range

Set PCode = Sheets("Data").Columns(5)
Set Shift = Sheets("Data").Columns(12)
Set TOI2 = Sheets("Data").Columns(27)

'Application.ScreenUpdating = False

lRow = Cells(Rows.Count, 1).End(xlUp).Row

    For x = 11 To lRow
    
    If Cells(x, 3) <> "" Then
        DCode = Left(Cells(x, 3), 6)
    End If
    
        For y = 6 To 13
            SCode = Trim(Cells(x, 4))
            FCode = SCode & DCode
    
            Cells(x, y).Select
            If Cells(x, 4) <> "" Then
            
                ActiveCell.FormulaR1C1 = "=COUNTIFS(" & PCode & "," & FCode & "," & Shift & ",R" & x & "C2," & TOI2 & ",R10C" & y & ")"

                Center
                
            Else: Cells(x, y) = ""
            
            End If
        Next y
        
        If Cells(x, 4) <> "" Then
        
            Cells(x, 14).Select
            ActiveCell.FormulaR1C1 = "=SUM(R" & x & "C6:R" & x & "C13))"
        
            Center
            
        Else: Cells(x, y) = ""
        
        End If
        
    Next x
    
    Cells(lRow + 2, 3).Value = "TOTAL"
    
    For i = 6 To 14
    
        Cells(lRow + 2, i).Select
        ActiveCell.FormulaR1C1 = "=SUBTOTAL(109,R11C" & i & ":R" & lRow & "C" & i & ")"
        
    Next i
    
    Cells(1, 1).Select
    
'    Application.ScreenUpdating = True
    
End Sub

Thank you

Dave

 

[starl]

You've set those columns to variables - but you haven't actually NAMED them.
Those variables are ranges. When you say "PCode" in your formula - you're putting everything that range is (address, formatting, etc), into the formula.
You can't do that.

But what you could do is put the address in the formula.. Pcode.Address