I am getting a Type Mismatch error where .Row is highlighted and I cannot figure out why this is happening. I commented the line that has the error. The line of code above this is similar but not generating the error message. These a ranges that vary in size from day to day. I am not sure if the problem is with the declaration or the the range itself. I tried adding a "+1+ and a "-1" after .Row, but I received the same error message. Any help is appreciated.
Sub DDS_SUMIFS()
'
' DDS_SUMIFS Macro
'
'
' ActiveCell.FormulaR1C1 = _
"=SUMIFS('Raw Data - DS'!R2C6:R14664C6,'Raw Data - DS'!R2C1:R14664C1,'Dock Door Status'!RC5,'Raw Data - DS'!R2C8:R14664C8,'Dock Door Status'!R32C)"
' ActiveCell.Select
'Selection.AutoFill Destination:=ActiveCell.Range("A1:C1"), Type:= _
xlFillDefault
' ActiveCell.Range("A1:C1").Select
'Selection.AutoFill Destination:=ActiveCell.Range("A1:C10"), Type:= _
xlFillDefault
'ActiveCell.Range("A1:C10").Select
'ActiveCell.Offset(1, 0).Range("A1").Activate
Dim DSQTY As Range
Dim NSQTY As Long
Dim DSDestWH, NSDestWH, DSWorkPool, NSWorkPool As Range
Dim RDDS, RDNS As Worksheet
Dim ABQ1, BFI4, CLE2, DEN3, DEN4, GEG1, LIT1, ORD5, ORF3, PAE2, PCW1, PDX9, SLC1, SMF1 As String
Set RDDS = Sheets("Raw Data - DS")
Set RDNS = Sheets("Raw Data - NS")
Set DSQTY = RDDS.Range("F2").End(xlUp).Row
Set NSQTY = RDNS.Range("F2").End(xlUp).Row '<----- Error: Object Required (NSQTY = is highlighted)
Set DSDestWH = RDDS.Range("A2").End(xlUp).Row
Set NSDestWH = RDNS.Range("A2").End(xlUp).Row
Set DSWorkPool = RDDS.Range("F2").End(xlUp).Row
Set NSWorkPool = RDNS.Range("F2").End(xlUp).Row
ABQ1 = "ABQ1"
BFI4 = "BFI4"
CLE2 = "CLE2"
DEN3 = "DEN3"
DEN4 = "DEN4"
GEG1 = "GEG1"
LIT1 = "LIT1"
ORD5 = "ORD5"
ORF3 = "ORF3"
PAE2 = "PAE2"
PCW1 = "PCW1"
PDX9 = "PDX9"
SLC1 = "SLC1"
SMF1 = "SMF1"
' PNYP Column
Range("H33") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, ABQ1, DSWorkPool, "PNYP")
Range("H34") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, BFI4, DSWorkPool, "PNYP")
Range("H35") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, CLE2, DSWorkPool, "PNYP")
Range("H36") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, DEN3, DSWorkPool, "PNYP")
Range("H37") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, DEN4, DSWorkPool, "PNYP")
Range("H38") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, GEG1, DSWorkPool, "PNYP")
Range("H39") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, LIT1, DSWorkPool, "PNYP")
Range("H40") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, ORD5, DSWorkPool, "PNYP")
Range("H41") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, ORF3, DSWorkPool, "PNYP")
Range("H42") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, PAE2, DSWorkPool, "PNYP")
Range("H43") = WorksheetFunction.SumIfs(NSQTY, NSDestWH, PCW1, NSWorkPool, "PNYP")
Range("H44") = WorksheetFunction.SumIfs(NSQTY, NSDestWH, PDX9, NSWorkPool, "PNYP")
Range("H45") = WorksheetFunction.SumIfs(NSQTY, NSDestWH, SLC1, NSWorkPool, "PNYP")
Range("H46") = WorksheetFunction.SumIfs(NSQTY, NSDestWH, SMF1, NSWorkPool, "PNYP")
' PP Column
Range("H33") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, ABQ1, DSWorkPool, "PP")
Range("H34") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, BFI4, DSWorkPool, "PP")
Range("H35") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, CLE2, DSWorkPool, "PP")
Range("H36") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, DEN3, DSWorkPool, "PP")
Range("H37") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, DEN4, DSWorkPool, "PP")
Range("H38") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, GEG1, DSWorkPool, "PP")
Range("H39") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, LIT1, DSWorkPool, "PP")
Range("H40") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, ORD5, DSWorkPool, "PP")
Range("H41") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, ORF3, DSWorkPool, "PP")
Range("H42") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, PAE2, DSWorkPool, "PP")
Range("H43") = WorksheetFunction.SumIfs(NSQTY, NSDestWH, PCW1, NSWorkPool, "PP")
Range("H44") = WorksheetFunction.SumIfs(NSQTY, NSDestWH, PDX9, NSWorkPool, "PP")
Range("H45") = WorksheetFunction.SumIfs(NSQTY, NSDestWH, SLC1, NSWorkPool, "PP")
Range("H46") = WorksheetFunction.SumIfs(NSQTY, NSDestWH, SMF1, NSWorkPool, "PP")
' LD Column
Range("H33") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, ABQ1, DSWorkPool, "LD")
Range("H34") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, BFI4, DSWorkPool, "LD")
Range("H35") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, CLE2, DSWorkPool, "LD")
Range("H36") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, DEN3, DSWorkPool, "LD")
Range("H37") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, DEN4, DSWorkPool, "LD")
Range("H38") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, GEG1, DSWorkPool, "LD")
Range("H39") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, LIT1, DSWorkPool, "LD")
Range("H40") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, ORD5, DSWorkPool, "LD")
Range("H41") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, ORF3, DSWorkPool, "LD")
Range("H42") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, PAE2, DSWorkPool, "LD")
Range("H43") = WorksheetFunction.SumIfs(NSQTY, NSDestWH, PCW1, NSWorkPool, "LD")
Range("H44") = WorksheetFunction.SumIfs(NSQTY, NSDestWH, PDX9, NSWorkPool, "LD")
Range("H45") = WorksheetFunction.SumIfs(NSQTY, NSDestWH, SLC1, NSWorkPool, "LD")
Range("H46") = WorksheetFunction.SumIfs(NSQTY, NSDestWH, SMF1, NSWorkPool, "LD")
End Sub
Something like this:
Dim DSQTY As Range, NSQTY As Range
Dim DSDestWH As Range, NSDestWH As Range
Dim DSWorkPool As Range, NSWorkPool As Range
Dim RDDS As Worksheet, RDNS As Worksheet
' Each variable must be declared separately unless they are of type Variant
' ... next lines of your code
With RDDS
Set DSQTY = .Range("F2", .Cells(.Rows.Count, "F").End(xlUp))
Set DSDestWH = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
Set DSWorkPool = .Range("F2", .Cells(.Rows.Count, "F").End(xlUp))
End With
With RDNS
Set NSQTY = .Range("F2", .Cells(.Rows.Count, "F").End(xlUp))
Set NSDestWH = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
Set NSWorkPool = .Range("F2", .Cells(.Rows.Count, "F").End(xlUp))
End With
' ... next lines of your code
Are you sure that DSQTY and DSWorkPool are in the same column?
And also NSQTY and NSWorkPool?