Using Mid with the If function.

[RogerP]

I can't believe I am having difficulty with this after over 15 years of coding Excel VBA macros.

I am trying to loop through a series of records and have set "i" as the row indicator. I am trying to examine an up to 25 character Tax ID code and pick up the 11th character to determine if it is 0 or 1-9. I can't figure it out.
The Tax ID code may be something like this: 1349-001-001-0000 or 1349-009-021-0000


If the field to be examined is in Cell "H2" and i = 2, I am coding my VBA macro as:

i = 2
Range("H" & i).Select
If Mid("H" & i & ",11,1")=0 then
my True instruction would go here. I want to use the '1' which follows that found '0' from the first example.
else
my False instruction would go here. I want to use the '21' which includes the '2' and the following '1' from the second example.
End If

I get a Compiler Error Message on the If statement. Any ideas? Thanks.

 

[Rick Rothstein]

Try it this way....

If Mid(Range("H" & i).Value, 11, 1) = 0 Then

 

[eiloken]

I can't believe I am having difficulty with this after over 15 years of coding Excel VBA macros.

I am trying to loop through a series of records and have set "i" as the row indicator. I am trying to examine an up to 25 character Tax ID code and pick up the 11th character to determine if it is 0 or 1-9. I can't figure it out.
The Tax ID code may be something like this: 1349-001-001-0000 or 1349-009-021-0000


If the field to be examined is in Cell "H2" and i = 2, I am coding my VBA macro as:

i = 2
Range("H" & i).Select
If Mid("H" & i & ",11,1")=0 then
my True instruction would go here. I want to use the '1' which follows that found '0' from the first example.
else
my False instruction would go here. I want to use the '21' which includes the '2' and the following '1' from the second example.
End If

I get a Compiler Error Message on the If statement. Any ideas? Thanks.

i dont think you need:

Range("H" & i).Select

and when you use mid function in vba, it should be:

Mid(Range("H" & i ) ,11,1)

 

[RogerP]

Being I failed to indicate that I was trying to set up an ActiveCell.Formula, I managed to get it to work with the input from "eiloken". Thank you for the quick response. Rick, thank you too.

If Mid(Range("H" & i), 11, 1) = 0 Then
Range("R" & i).Select
ActiveCell.Formula = "=Mid(H" & i & ", 12, 2)"
Else
Range("R" & i).Select
ActiveCell.Formula = "=Mid(H" & i & ", & 11, 3)"
End If

 

[RoryA]

You still don't need to select the cell to add a formula:

If Mid(Range("H" & i), 11, 1) = 0 Then
Range("R" & i).Formula = "=Mid(H" & i & ", 12, 2)"
Else
Range("R" & i).Formula = "=Mid(H" & i & ", & 11, 3)"
End If