CODEWITHSUNDEEP
excelvba
kit99
kit99

Reputation: 187

Excel vba - how to find digit no 8 in cell and evaluate if it's the correct one

Got this vba so far:

For Each cell In Range("A2", Range("A" & Rows.Count).End(xlUp))
If Len(cell.Value) = 11 Then
cell.Copy Destination:=Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next cell

I need to add some code behind "11" in the second line, but I can't figure out how to write it. What I want to do is to add something like "and digit number 8 (of 11) is 1 or 3 or 5 or 7 or 9 then". Anyone that can help me out?

Upvotes: 1

Views: 1453

Answers (2)

Maddy
Maddy

Reputation: 781

Try this

For Each cell In Range("A2", Range("A" & Rows.Count).End(xlUp))
    If Len(cell.Value) = 11 Then
        Select Case Mid(cell.Value, 8, 1)
        Case 1, 3, 5, 7, 9
             cell.Copy Destination:=Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
        End Select
    End If
Next cell

Upvotes: 4

ashleedawg
ashleedawg

Reputation: 21639

I couldn't follow your explanation but according to your title, you need to check if the 8th character is a string (from a cell, etc) is "the right one":

Function Check8thCharacter(myString As String, theRightOne As String) As Boolean
    Check8thCharacter = (Mid(myString, 8, 1) = theRightOne)
End Function

Here's an example of it in use:

Sub Digit8()
    Range("A1") = "ABCDEFGHIJKLM"
    Const testRightOne = "H"
    If Check8thCharacter(Range("A1"), testRightOne) Then
        MsgBox "It's the right one!"
    Else
        MsgBox "It's the wrong one."
    End If
End Sub

Another look at your question makes me wonder if you're actually trying to determine whether the eighth character of a cell is an odd number, which is only a slight variation:

Function Is8thOdd(rg As Range) As Boolean
    On Error Resume Next 'error will also return false
    Is8thOdd = (Mid(rg, 8, 1) / 2 <> Mid(rg, 8, 1) \ 2)
End Function

Here's an example of it in use: 

Sub Digit8()
    If Is8thOdd(Range("A1")) Then
        MsgBox "Yes, it's odd"
    Else
        MsgBox "No, it's not odd."
    End If
End Sub

Upvotes: 0

Related Questions