Ark68
Well-known Member
- Joined
- Mar 23, 2004
- Messages
- 4,665
- Office Version
- 365
- 2016
- Platform
- Windows
I have this code below that steps through all the cells of a range and applies code depending on the cell's content.
In one case, if the cell contains a time value, ignore it and move on. (code highlighted as blue)
Here is a sample data in which I'm encountering problems:
When the code assessing the cells reaches Q17 and Q18, the code in blue does not catch it and the routine continues to be caught at the else statement. It shouldn't get that far as those cells have "time" values in them (?). Interestingly, unlike cells Q15, Q15 and Q16 where the time values are text, Q17 and Q18 are numeric. I would have thought if I were going to have problems it would be with the text values.
Anyone have any suggestions on how to overcome this? If any cell is or looks like a time (in the format "h:mmA/P" that cell should be ignored.
In one case, if the cell contains a time value, ignore it and move on. (code highlighted as blue)
Rich (BB code):
Set rngpda = ws9.Range("H13:Q" & rwpdaed)
For Each cell In rngpda
ccnt = ccnt + 1
cval2 = cell.Value
Debug.Print cell.Address & " : " & cval2
If cval2 = "" Then
Debug.Print "Service ignored: Empty"
ElseIf Right(cval2, 3) = " AM" Or Right(cval2, 3) = " PM" Then
Debug.Print "Pre-service ignored: " & cval2
ElseIf IsDate(Format(Now(), "yyyy-mm-dd ") & cval2) = True Then
Debug.Print "Service ignored (time): " & cval2
ElseIf cval2 = "NA" Or Right(cval2, 3) = "NR" Then
Debug.Print "Service ignored: " & cval2
ElseIf cval2 = "AUTO" Or cval2 = "USER" Then
Debug.Print "Service ignored: " & cval2
Else
cntcval2 = Application.WorksheetFunction.CountIf(ws_staff.Columns(4), cval2)
If cntcval2 > 0 Then
Debug.Print "Match: " & cval2 & " [" & l & "]"
Else
'Stop
MsgBox cval2 & " at " & cell.Address & " is not in the roster." & Chr(13) & "Please adjust the name to an employee that is working", vbCritical, "Error: Staff mismatch"
cell.Font.Color = vbRed
frm_sniroster.Show
tsnr = tsnr + cntrpl
End If
End If
Next cell
Here is a sample data in which I'm encountering problems:
WS 28-Jun-22.xlsx | |||
---|---|---|---|
Q | |||
13 | 8:30P | ||
14 | |||
15 | 9:30P | ||
16 | 9:30P | ||
17 | 8:00P | ||
18 | 8:00P | ||
RPL |
When the code assessing the cells reaches Q17 and Q18, the code in blue does not catch it and the routine continues to be caught at the else statement. It shouldn't get that far as those cells have "time" values in them (?). Interestingly, unlike cells Q15, Q15 and Q16 where the time values are text, Q17 and Q18 are numeric. I would have thought if I were going to have problems it would be with the text values.
Anyone have any suggestions on how to overcome this? If any cell is or looks like a time (in the format "h:mmA/P" that cell should be ignored.