I am writing a vba subroutine in excel vba that totals the quantity of lbs sold to customers for varying years and for some reason I am getting a compile error with the year function. Could someone please explain why? Here's my code:
Private Sub lbsPerCustPerYear_Click()
Dim i As Integer
Dim Cust As String
Dim Cust2 As String
Dim Total As Integer
Dim Output As Range
Dim Output2 As Range
Dim Year1 As Date
Total = 0
Year1 = Range("K6").Value
Cust = Range("C6").Value
Cust2 = Range("C7").Value
Set Output = Sheets("Sheet2").Cells(2, 2)
Set Output2 = Sheets("Sheet2").Cells(2, 3)
For i = 1 To 14750
Year1 = Range("K5").Offset(i, 0).Value
If IsDate(Sheets("Sheet1").Cells(i, 11)) Then
If Cust = Cust2 And Year(Sheets("Sheet1").Cells(i, 11)) = 2012 Then
Total = Range("Q5").Offset(i, 0).Value + Range("Q6").Offset(i, 0).Value
Cust = Range("C5").Offset(i, 0).Value
Cust2 = Range("C6").Offset(i, 0).Value
Output.Value = Cust
Output2.Value = Total
Else
Cust = Range("C5").Offset(i, 0).Value
Cust2 = Range("C6").Offset(i, 0).Value
Year1 = Range("K5").Offset(i, 0).Value
Set Output = Output.Offset(1, 0)
Set Output2 = Output2.Offset(1, 0)
End If
Else
Cust = Range("C5").Offset(i, 0).Value
Cust2 = Range("C6").Offset(i, 0).Value
Year1 = Range("K5").Offset(i, 0).Value
Set Output = Output.Offset(1, 0)
Set Output2 = Output2.Offset(1, 0)
End If
Next i
End Sub
The error is in the line:
If Cust = Cust2 And Year(Sheets("Sheet1").Cells(i, 11).Value) = 2012 Then
Thanks in advance!
Does each cell in column 11 contain a valid date or year?
You could check this first:
If isdate(Sheets("Sheet1").Cells(i, 11).Value) then
If Cust = Cust2 And Year(Sheets("Sheet1").Cells(i, 11).Value) = 2012 Then
......
end if
else
'handle invalid date
end if
Something else I've noticed is you've used Year as a variable name, change this to dtYear or something as this may conflict with the Year function in the object library.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments