As part of a sub routine I am trying to sum by color. But I am getting "'Runtime Error '13': Type Miss match" on the line "For Each cl In sRange"
If any one can put me in the right direction then Thanks in advance.
The full code is below.
Sub Reconcile()
Dim ActWs As Worksheet
Dim ColA As Long ' ColA = Column A
Dim R As Long 'R = Row
Set ActWs = ThisWorkbook.ActiveSheet 'ActWs = active Worksheet.
'ActWsLastRow = The number of rows to the last entry in Column A
ActWsLastRow = ActWs.Range("A" & Rows.Count).End(xlUp).Row
ActWs.Range("A" & ActWsLastRow).Offset(4, 1).Value = "opening balance"
ActWs.Range("A" & ActWsLastRow).Offset(4, 2).Value = Range("C8")
ActWs.Range("A" & ActWsLastRow).Offset(5, 1).Value = "Add Sales"
ActWs.Range("A" & ActWsLastRow).Offset(5, 2).Value = Range("A" & ActWsLastRow).Offset(-3, 2)
ActWs.Range("A" & ActWsLastRow).Offset(6, 1).Value = "Total OB + Sales"
ActWs.Range("A" & ActWsLastRow).Offset(6, 2).Value = Range("A" & ActWsLastRow).Offset(4, 2) + _
Range("A" & ActWsLastRow).Offset(5, 2)
ActWs.Range("A" & ActWsLastRow).Offset(7, 1).Value = "Deduct purcheses and Transfers"
ActWs.Range("A" & ActWsLastRow).Offset(7, 2).Value = Range("A" & ActWsLastRow).Offset(-3, 3)
ActWs.Range("A" & ActWsLastRow).Offset(8, 1).Value = "Balance in Zero"
ActWs.Range("A" & ActWsLastRow).Offset(8, 2).Value = Range("A" & ActWsLastRow).Offset(6, 2) - _
Range("A" & ActWsLastRow).Offset(7, 2)
ActWs.Range("A" & ActWsLastRow).Offset(9, 1).Value = "Deduct Credits to be paid next month"
' Function SumByColor(CellColor As Range, rRange As Range)
Dim cSum As Double
Dim ColIndex As Integer
Dim sRange1 As Range
Set sRange1 = Range(Range("A" & ActWsLastRow).Offset(-54, 3), Range("A" & ActWsLastRow).Offset(-4, 3)) _
' Or use "Set sRange1 = Range("D" & ActWsLastRow - 54).Resize(51)"
ColIndex = 43
For Each cl In sRange 'Runtime Error '13': Type Miss match
If cl.Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.Sum(cl, cSum)
End If
Next cl
' SumByColor = cSum
ActWs.Range("A" & ActWsLastRow).Offset(9, 2).Value = cSum
End Sub
If any one can put me in the right direction then Thanks in advance.
The full code is below.
Sub Reconcile()
Dim ActWs As Worksheet
Dim ColA As Long ' ColA = Column A
Dim R As Long 'R = Row
Set ActWs = ThisWorkbook.ActiveSheet 'ActWs = active Worksheet.
'ActWsLastRow = The number of rows to the last entry in Column A
ActWsLastRow = ActWs.Range("A" & Rows.Count).End(xlUp).Row
ActWs.Range("A" & ActWsLastRow).Offset(4, 1).Value = "opening balance"
ActWs.Range("A" & ActWsLastRow).Offset(4, 2).Value = Range("C8")
ActWs.Range("A" & ActWsLastRow).Offset(5, 1).Value = "Add Sales"
ActWs.Range("A" & ActWsLastRow).Offset(5, 2).Value = Range("A" & ActWsLastRow).Offset(-3, 2)
ActWs.Range("A" & ActWsLastRow).Offset(6, 1).Value = "Total OB + Sales"
ActWs.Range("A" & ActWsLastRow).Offset(6, 2).Value = Range("A" & ActWsLastRow).Offset(4, 2) + _
Range("A" & ActWsLastRow).Offset(5, 2)
ActWs.Range("A" & ActWsLastRow).Offset(7, 1).Value = "Deduct purcheses and Transfers"
ActWs.Range("A" & ActWsLastRow).Offset(7, 2).Value = Range("A" & ActWsLastRow).Offset(-3, 3)
ActWs.Range("A" & ActWsLastRow).Offset(8, 1).Value = "Balance in Zero"
ActWs.Range("A" & ActWsLastRow).Offset(8, 2).Value = Range("A" & ActWsLastRow).Offset(6, 2) - _
Range("A" & ActWsLastRow).Offset(7, 2)
ActWs.Range("A" & ActWsLastRow).Offset(9, 1).Value = "Deduct Credits to be paid next month"
' Function SumByColor(CellColor As Range, rRange As Range)
Dim cSum As Double
Dim ColIndex As Integer
Dim sRange1 As Range
Set sRange1 = Range(Range("A" & ActWsLastRow).Offset(-54, 3), Range("A" & ActWsLastRow).Offset(-4, 3)) _
' Or use "Set sRange1 = Range("D" & ActWsLastRow - 54).Resize(51)"
ColIndex = 43
For Each cl In sRange 'Runtime Error '13': Type Miss match
If cl.Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.Sum(cl, cSum)
End If
Next cl
' SumByColor = cSum
ActWs.Range("A" & ActWsLastRow).Offset(9, 2).Value = cSum
End Sub