Mid and Mod to extract a number from string

Hello, I am trying to grab a number from a string to determine if it is even or odd. I am using the following function

  Dim i As Integer
    For i = 2 To 944
        If Mid("H" & i, 8, 8) = "8" And Mid("H" & i, 8, 9) Mod 2 <> 0 Then
            Range("A" & i & ":J" & i).Copy Destination:=Sheets(test)
    End If

However when I run it I get the error message type mismatch. I am assuming this is due to the fact I am running a math function on a string? Any suggestions or change I can use to get this to work right? I saw one example that used the .NumberFormat but that threw errors as well. What is the best way to parse a string character into type int?

Hi,

It would help if you attached your workbook example

In any case change the "8" to the number 8 only

  Dim i As Integer
    For i = 2 To 944
        If Mid("H" & i, 8, 8) = 8 And Mid("H" & i, 8, 9) Mod 2 <> 0 Then
            Range("A" & i & ":J" & i).Copy Destination:=Sheets(test)
    End If

I have included the work book. What I am pulling from is a string so I thought that it had to be included in quotes.

Also, be sure to refer to a range value within the mid function. Without testing the code and seeing the workbook example, at the moment, I don't fully understand it

I just tried to specify the range in Mid and still getting the same error.

I misunderstood Mid. I have changed it to return only 1 character.

Originally Posted by mikerickson

You are looping through the strings "H2", "H3", "H4", ...
Also, Mid(anyString, 8, 8) will never ="8" (unless anyString = "8') since the Mid is returning 8 characters.
Also, the destination argument of Copy should be a range, not a sheet.

If you want to loop through the cells H1, H2, etc

Dim i As Integer

For i = 2 To 944
    With Range("A" & i)
        If (Mid(CStr(.Cells(1, 8).Value)), 8, 1) = "8") And ((Val(Mid(CStr(.Cells(1, 8).Value)) & i, 8, 9)) Mod 2) <> 0) Then
            .Resize(1, 11).Copy Destination:=Sheets(test).Range(.Address)
        End If
    End With
Next i

That is throwing a Syntax error.