Portuguese Social Format NOT desired

[Kigol]

I convert an entire column of 9 digit identifiers using:

Columns("A:A").NumberFormat = "000000000"

I then try to compare the resulting values cell by cell to Zero. I get
a type mismatch error and when I check the cell formatting in excel it
is formatted under "Special" as a Portuguese Social Security Number.
I'm at a loss.....

 

[Dave Peterson]

Do you have an error in column A?

If not what's in the cell that causes the error?

 

[Dave Peterson]

ps. What version of excel are you using?

I convert an entire column of 9 digit identifiers using:

Columns("A:A").NumberFormat = "000000000"

I then try to compare the resulting values cell by cell to Zero. I get
a type mismatch error and when I check the cell formatting in excel it
is formatted under "Special" as a Portuguese Social Security Number.
I'm at a loss.....

 

[Kigol]

ps. What version of excel are you using?

Well I discovered the type mismatch problem was due to another issue.
But when I try to test the cell value with the following code it skips
over the condition as if there is something besides zero in the cell.

For z = 0 To x
Range("C2").Select

If IsEmpty(ActiveCell.Offset(z, 0)) Then '<---Tests for empty cell to
be populated

If IsEmpty(ActiveCell.Offset(0, -2)) Then '<---Tests for empty cells
in column A
ActiveCell.Offset(z, 0).FormulaR1C1 =
"=IF(ISNA(VLOOKUP(RC[-1],Symbols!C[-1]:C,
2,0)),VLOOKUP(TRIM(RC[-1]),Symbols!C[-1]:C,2,0),VLOOKUP(RC[-1],Symbols!
C[-1]:C,2,0))"

ElseIf ActiveCell.Offset(0, -2).Value = "0" Then '<b><-----------THIS
IS THE CONDITION I AM TRYING TO TEST<b>

ActiveCell.Offset(z, 0).FormulaR1C1 =
"=IF(ISNA(VLOOKUP(RC[-1],Symbols!C[-1]:C,
2,0)),VLOOKUP(TRIM(RC[-1]),Symbols!C[-1]:C,2,0),VLOOKUP(RC[-1],Symbols!
C[-1]:C,2,0))"

Else <-If there are no empty cells use this cell as reference.

ActiveCell.Offset(z, 0).FormulaR1C1 =
"=IF(ISNA(VLOOKUP(RC[-2],Symbols!C[-2]:C,
3,0)),VLOOKUP(TRIM(RC[-2]),Symbols!C[-2]:C,3,0),VLOOKUP(RC[-2],Symbols!
C[-2]:C,3,0))"

End If
End If
Next z

 

[Dave Peterson]

I'd drop the double quotes:
ElseIf ActiveCell.Offset(0, -2).Value = "0"
becomes:
ElseIf ActiveCell.Offset(0, -2).Value = 0

And test it out.

ps. What version of excel are you using?

Well I discovered the type mismatch problem was due to another issue.
But when I try to test the cell value with the following code it skips
over the condition as if there is something besides zero in the cell.

For z = 0 To x
Range("C2").Select

If IsEmpty(ActiveCell.Offset(z, 0)) Then '<---Tests for empty cell to
be populated

If IsEmpty(ActiveCell.Offset(0, -2)) Then '<---Tests for empty cells
in column A
ActiveCell.Offset(z, 0).FormulaR1C1 =
"=IF(ISNA(VLOOKUP(RC[-1],Symbols!C[-1]:C,
2,0)),VLOOKUP(TRIM(RC[-1]),Symbols!C[-1]:C,2,0),VLOOKUP(RC[-1],Symbols!
C[-1]:C,2,0))"

ElseIf ActiveCell.Offset(0, -2).Value = "0" Then '<b><-----------THIS
IS THE CONDITION I AM TRYING TO TEST<b>

ActiveCell.Offset(z, 0).FormulaR1C1 =
"=IF(ISNA(VLOOKUP(RC[-1],Symbols!C[-1]:C,
2,0)),VLOOKUP(TRIM(RC[-1]),Symbols!C[-1]:C,2,0),VLOOKUP(RC[-1],Symbols!
C[-1]:C,2,0))"

Else <-If there are no empty cells use this cell as reference.

ActiveCell.Offset(z, 0).FormulaR1C1 =
"=IF(ISNA(VLOOKUP(RC[-2],Symbols!C[-2]:C,
3,0)),VLOOKUP(TRIM(RC[-2]),Symbols!C[-2]:C,3,0),VLOOKUP(RC[-2],Symbols!
C[-2]:C,3,0))"

End If
End If
Next z

 

[Kigol]

I'd drop the double quotes:
ElseIf ActiveCell.Offset(0, -2).Value = "0"
becomes:
ElseIf ActiveCell.Offset(0, -2).Value = 0

And test it out.

Well I discovered the type mismatch problem was due to another issue.
But when I try to test the cell value with the following code it skips
over the condition as if there is something besides zero in the cell.

For z = 0 To x
Range("C2").Select

If IsEmpty(ActiveCell.Offset(z, 0)) Then '<---Tests for empty cell to
be populated

If IsEmpty(ActiveCell.Offset(0, -2)) Then '<---Tests for empty cells
in column A
ActiveCell.Offset(z, 0).FormulaR1C1 =
"=IF(ISNA(VLOOKUP(RC[-1],Symbols!C[-1]:C,
2,0)),VLOOKUP(TRIM(RC[-1]),Symbols!C[-1]:C,2,0),VLOOKUP(RC[-1],Symbols!
C[-1]:C,2,0))"

ElseIf ActiveCell.Offset(0, -2).Value = "0" Then '<b><-----------THIS
IS THE CONDITION I AM TRYING TO TEST<b>

ActiveCell.Offset(z, 0).FormulaR1C1 =
"=IF(ISNA(VLOOKUP(RC[-1],Symbols!C[-1]:C,
2,0)),VLOOKUP(TRIM(RC[-1]),Symbols!C[-1]:C,2,0),VLOOKUP(RC[-1],Symbols!
C[-1]:C,2,0))"

Else <-If there are no empty cells use this cell as reference.

ActiveCell.Offset(z, 0).FormulaR1C1 =
"=IF(ISNA(VLOOKUP(RC[-2],Symbols!C[-2]:C,
3,0)),VLOOKUP(TRIM(RC[-2]),Symbols!C[-2]:C,3,0),VLOOKUP(RC[-2],Symbols!
C[-2]:C,3,0))"

End If
End If
Next z

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Tried that. Tried removing the .Value. Tried all possible
combinations. When I do =Cell=0 on the sheet directly it returns TRUE
so I know it is a zero value. For some reason VBA isn't seeing it that
way.....

 

[Dave Peterson]

Maybe it's time to make sure you're looking at the cell that you really want:

msgbox activecell.offset(0,-2).value & vblf & activecell.offset(0,-2).address

Pepper a few of those into your code to check things out.

I'd drop the double quotes:
ElseIf ActiveCell.Offset(0, -2).Value = "0"
becomes:
ElseIf ActiveCell.Offset(0, -2).Value = 0

And test it out.

ps. What version of excel are you using?

Kigol wrote:

I convert an entire column of 9 digit identifiers using:

Columns("A:A").NumberFormat = "000000000"

I then try to compare the resulting values cell by cell to Zero. I get
a type mismatch error and when I check the cell formatting in excel it
is formatted under "Special" as a Portuguese Social Security Number.
I'm at a loss.....

Dave Peterson

Well I discovered the type mismatch problem was due to another issue.
But when I try to test the cell value with the following code it skips
over the condition as if there is something besides zero in the cell.

For z = 0 To x
Range("C2").Select

If IsEmpty(ActiveCell.Offset(z, 0)) Then '<---Tests for empty cell to
be populated

If IsEmpty(ActiveCell.Offset(0, -2)) Then '<---Tests for empty cells
in column A
ActiveCell.Offset(z, 0).FormulaR1C1 =
"=IF(ISNA(VLOOKUP(RC[-1],Symbols!C[-1]:C,
2,0)),VLOOKUP(TRIM(RC[-1]),Symbols!C[-1]:C,2,0),VLOOKUP(RC[-1],Symbols!
C[-1]:C,2,0))"

ElseIf ActiveCell.Offset(0, -2).Value = "0" Then '<b><-----------THIS
IS THE CONDITION I AM TRYING TO TEST<b>

ActiveCell.Offset(z, 0).FormulaR1C1 =
"=IF(ISNA(VLOOKUP(RC[-1],Symbols!C[-1]:C,
2,0)),VLOOKUP(TRIM(RC[-1]),Symbols!C[-1]:C,2,0),VLOOKUP(RC[-1],Symbols!
C[-1]:C,2,0))"

Else <-If there are no empty cells use this cell as reference.

ActiveCell.Offset(z, 0).FormulaR1C1 =
"=IF(ISNA(VLOOKUP(RC[-2],Symbols!C[-2]:C,
3,0)),VLOOKUP(TRIM(RC[-2]),Symbols!C[-2]:C,3,0),VLOOKUP(RC[-2],Symbols!
C[-2]:C,3,0))"

End If
End If
Next z

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Tried that. Tried removing the .Value. Tried all possible
combinations. When I do =Cell=0 on the sheet directly it returns TRUE
so I know it is a zero value. For some reason VBA isn't seeing it that
way.....

 

[Kigol]

Maybe it's time to make sure you're looking at the cell that you really want:

msgbox activecell.offset(0,-2).value & vblf & activecell.offset(0,-2).address

Pepper a few of those into your code to check things out.

I'd drop the double quotes:
ElseIf ActiveCell.Offset(0, -2).Value = "0"
becomes:
ElseIf ActiveCell.Offset(0, -2).Value = 0
And test it out.
Kigol wrote:
ps. What version of excel are you using?
Kigol wrote:
I convert an entire column of 9 digit identifiers using:
Columns("A:A").NumberFormat = "000000000"
I then try to compare the resulting values cell by cell to Zero. I get
a type mismatch error and when I check the cell formatting in excel it
is formatted under "Special" as a Portuguese Social Security Number.
I'm at a loss.....
--
Dave Peterson
Well I discovered the type mismatch problem was due to another issue.
But when I try to test the cell value with the following code it skips
over the condition as if there is something besides zero in the cell.
For z = 0 To x
Range("C2").Select
If IsEmpty(ActiveCell.Offset(z, 0)) Then '<---Tests for empty cell to
be populated
If IsEmpty(ActiveCell.Offset(0, -2)) Then '<---Tests for empty cells
in column A
ActiveCell.Offset(z, 0).FormulaR1C1 =
"=IF(ISNA(VLOOKUP(RC[-1],Symbols!C[-1]:C,
2,0)),VLOOKUP(TRIM(RC[-1]),Symbols!C[-1]:C,2,0),VLOOKUP(RC[-1],Symbols!
C[-1]:C,2,0))"
ElseIf ActiveCell.Offset(0, -2).Value = "0" Then '<b><-----------THIS
IS THE CONDITION I AM TRYING TO TEST<b>
ActiveCell.Offset(z, 0).FormulaR1C1 =
"=IF(ISNA(VLOOKUP(RC[-1],Symbols!C[-1]:C,
2,0)),VLOOKUP(TRIM(RC[-1]),Symbols!C[-1]:C,2,0),VLOOKUP(RC[-1],Symbols!
C[-1]:C,2,0))"
Else <-If there are no empty cells use this cell as reference.
ActiveCell.Offset(z, 0).FormulaR1C1 =
"=IF(ISNA(VLOOKUP(RC[-2],Symbols!C[-2]:C,
3,0)),VLOOKUP(TRIM(RC[-2]),Symbols!C[-2]:C,3,0),VLOOKUP(RC[-2],Symbols!
C[-2]:C,3,0))"
End If
End If
Next z

Tried that. Tried removing the .Value. Tried all possible
combinations. When I do =Cell=0 on the sheet directly it returns TRUE
so I know it is a zero value. For some reason VBA isn't seeing it that
way.....

--

Dave Peterson- Hide quoted text -

- Show quoted text -

After you said that I went back and checked. The zeros in the offset
statements should be variable z instead to make them dynamic. Wow
can't believe I missed that. Thanks for the hlp Dave. Cheers.