Mid and Mod to extract a number from string

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.