Trying To Avoid Cells With A Time Value

[Ark68]

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)

                    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.

 

[6StringJazzer]

When asking for help with code it is better to show all the code because the problem might not be where you think it is. In this case I need to see how you are declaring variable cval2.

You are converting the data to text before you test it for being date/time data, so the result will always be FALSE. Change your code to this:

                        ElseIf IsDate(cval2) Then

However, you should really test the cell directly:

                        ElseIf IsDate(cell) Then

(BTW you don't have to compare a Boolean function to True, because it will already be either True or False.)

 

[Ark68]

Hello Jeff!
'cval2' is a variable declared publicly as string in another module. I have a lot of modules including the one where that is declared that to post would have been a lot. With so much code I'm hesitate to post pages of what I think might be redundant.

I replaced...

ElseIf IsDate(Format(Now(), "yyyy-mm-dd ") & cval2) = True Then

with your suggestion ...

ElseIf IsDate(cell) Then

And the (true) time value in that cell isn't caught. If the value in cell = 8:00 PM, the user gets the msgbox pop up saying:
"0333333333333333 at $Q$17 is not in the roster."

Hope this helps?

 

[Alex Blakenburg]

It is not a good idea to be mixing time "value" and time as "text" in the same column.
Given what you are trying to do and that you have cval2 as String, if you change cval2 = cell.Value to

cval2 = cell.Text

You should then be able to drop the ElseIf IsDate line.

For Context Only:

                    For Each cell In rngpda
                        ccnt = ccnt + 1
                        'cval2 = cell.Value
                        cval2 = cell.Text

 

[Ark68]

Thank you Alex, I will implement this next time I have conflicting data to test it on. I think it will be rare to have the contents of the cells as numeric values. I think the problem lies in the data source that provides the value to this worksheet. That data source might be fed by different sources using different data entry methods. I think your solution will ensure that data is always text.

 

[Alex Blakenburg]

Sounds good. Let us know how you go.

By the way cell.text is not converting what is in the cell to text but it reads the cell as displayed .
So the cell might have the time as 0.333333333333333 being numeric value of the time in the cell, the cell is formatted to display 08:00 AM, then value and value2 will return 0.333333333333333 while cell.text will return 08:00 AM.

 

[Ark68]

Hi Alex. Excel sure is powerful when one realizes how different functions work within. When I first started using excel, I saw it simply as a place to make calculations on a grid. Then I discovered basic VBA, and with each lesson I get taught here by the good folk of Mr. Excel, I'm gain8ing even greater respect. Thank you.

I did make the change as suggested, but found I still needed to include

ElseIf IsDate(Format(Now(), "yyyy-mm-dd ") & cval2)

in order to bypass anything that could be a time. (whether a text representation or a true value) . Perhaps I did something wrong that would require it as you had indicated it shouldn't be needed anymore.

Happy to say, I'm getting the results I'm looking for so far in my testing.

 

[Alex Blakenburg]

Glad to hear that you got it working. Thanks for letting us know.