EXCEL VBA Combo box filter not working with numerical value but does with text value

[jocotterellash]

Dear all,

My combox below called cmbyear works fine filtering data when using a text values but doesn't work with numerical values.
I would like to filter on year (the data is in column 14) on shstaff. My report data is called emprpt1.
The combo box (cmbyear) uses rowsource in a named range and works for text values and not numerical values. I think I've formatted all correctly.
Slightly baffled on this one, many thanks in advance.






Sub employeeReport()

'employee column
shstaffEmpID = 1
shstaffFirstName = 2
shstaffLastName = 3
shstaffcountry = 4
shstaffComplete = 5 'works fine here
shstaffdepartment = 6
shstaffZip = 7
shstaffRegion = 8
shstaffPhone = 9
shstaffEmail = 10
shstaffMF = 11
shstaffETH = 12
shstaffDOB = 13
shstaffyear = 14 ' this column has year (numerical value) and is a problem
shstaffreg_group = 15
shstaffgender = 16
shstaffDateofbirth = 17
shstaffstatus = 18







'report cols
rEmpID = 1
rName = 2
rPhone = 3
rEmail = 4
rStatus = 5
ryear = 6

'last row
emplr = shStaff.Cells(Rows.Count, 1).End(xlUp).row

'rpt lr
rptlr = emprpt1.Cells(Rows.Count, 1).End(xlUp).row
'clear
emprpt1.range("a2:g" & rptlr + 1).ClearContents

rptRow = 2 'starting row

'loop
For empRow = 2 To emplr


If Me.cmbyear <> Empty And shStaff.Cells(empRow, shstaffyear) <> Me.cmbyear Then
GoTo nextRow
End If

this below text value filter work fine


' If Me.cmbyear <> Empty And shStaff.Cells(empRow, shstaffcomplete) <> Me.cmbyear Then
' GoTo nextRow
' End If




emprpt1.Cells(rptRow, rEmpID) = shStaff.Cells(empRow, shstaffEmpID)
emprpt1.Cells(rptRow, rName) = shStaff.Cells(empRow, shstaffFirstName) '& " " & shStaff.Cells(empRow, shstaffLastName)
emprpt1.Cells(rptRow, rPhone) = shStaff.Cells(empRow, shstaffPhone)
emprpt1.Cells(rptRow, rEmail) = shStaff.Cells(empRow, shstaffEmail)
emprpt1.Cells(rptRow, rStatus) = shStaff.Cells(empRow, shstaffstatus)
emprpt1.Cells(rptRow, ryear) = shStaff.Cells(empRow, shstaffyear)
'increase row num
rptRow = rptRow + 1



nextRow:
Next empRow



End Sub