How can I validate all characters in a string are only letters or numbers

[AndyTampa]

I posted a similar question last year titled Count cells that contain 6 characters or numbers but no spaces and received many wonderful responses, but my goal has evolved and I'm not getting any more responses in that conversation. Please forgive me if this appears to be a duplicate. It isn't.

With everything I'm being asked to check in this spreadsheet, I thought a VBA option would be best. A person using this sheet could enter all their data and just click one button to validate their input. Once validated, they could click another button to create a proper sheet without the code. With all the input I received, there were two that provided the correct results but I can't get those formulas to work in VBA. I no longer need to count cells with alphanumeric strings. My macro is running down each row and checking several items before moving on to the next row.

I am trying to check the cells to see that they are in the right format. That format is 6 characters of only numbers 0-9 or capital letters A-Z with not spaces or other characters. My macro capitalizes the cell before testing it. But if it fails the test, I want to highlight the cell and increment an error counter.

In that thread, on post #19, jtakw provided this Data Validation formula:

AND(LEN(A1)=6,ISNUMBER(SUMPRODUCT(FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"))))

When I tried to use this in an IF statement, VBA balked at the INDIRECT function.

On post #39, Rick Rothstein provided this Data Validation formula:

IF(LEN(A1)=6,(SUMPRODUCT((ABS(CODE(MID(LEFT(A1,6),ROW($1:$6),1))-69)<=21)*(ABS(CODE(MID(LEFT(A1,6),ROW($1:$6),1))-61)>3))=6))

When I tried to use this in the same IF statement, VBA balked at the dollar signs, so I removed them and it balked at the colons. I changed the colons to commas and VBA balked at the ROW function.

This is what I have in a For/Next loop (I've commented them out because they both failed):

'    If Cell.Value = Len("A" & RNum) = 6 And Cell.Value = IsNumber(SumProduct(Find(Mid("A" & RNum, Row(INDIRECT("1:" & Len("A" & RNum))), 1), "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"))) Then

'    If Cell.Value = Len("A" & RNum) = 6 And Cell.Value = SumProduct((Abs(CODE(Mid(Left("A" & RNum, 6), Row($1:$6), 1)) - 69) <= 21) * (Abs(CODE(Mid(Left("A" & RNum, 6), Row($1:$6), 1)) - 61) > 3)) = 6 Then

        End If

    Else
        Cells(RNum, 1).Interior.Color = RGB(0, 0, 125)
        CNumErr = CNumErr + 1
        Range("J3").Value = CNumErr & " Invalid Case Numbers"
    End If

I liked these formulas for DV, because they correctly identified a blank cell as being an invalid case number. Now, it would be really useful if there was already an Excel function that can do this.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I think I still need to make it two separate checks instead of combining the ranges since I want an error for each one.

It can possibly be done in a single loop but to check & suggest a code it would be good to see your full current code.
Also, will the data to check start on the same row in both columns?
Will the data to check end on the same row in both columns?
Could the two result be placed in two different cells rather than both in J4?

 

[AndyTampa]

I can't put my code in from my phone right now. I did rearrange the tests to use NOT like and they are working. J4 is a counter of errors for invalid data so each cell with an error bumps the counter. Using one test for two cells would require a more complex statement. Two individual tests just seems more straight-forward. To answer the question, every test is performed on the same row before moving on to the next row. On a side note, why can't I put separate paragraphs in here using my phone? They keep reverting to a space. .