| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Portuguese Social Format NOT desired
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..... |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Portuguese Social Format NOT desired
Do you have an error in column A?
If not what's in the cell that causes the error? 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Portuguese Social Format NOT desired
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Portuguese Social Format NOT desired
On Oct 11, 1:43 pm, Dave Peterson 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Portuguese Social Format NOT desired
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: On Oct 11, 1:43 pm, Dave Peterson 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 -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Portuguese Social Format NOT desired
On Oct 11, 5:13 pm, Dave Peterson wrote:
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: On Oct 11, 1:43 pm, Dave Peterson 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 -- 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..... |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Portuguese Social Format NOT desired
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. Kigol wrote: On Oct 11, 5:13 pm, Dave Peterson wrote: 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: On Oct 11, 1:43 pm, Dave Peterson 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 -- 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Portuguese Social Format NOT desired
On Oct 11, 8:37 pm, Dave Peterson wrote:
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. Kigol wrote: On Oct 11, 5:13 pm, Dave Peterson wrote: 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: On Oct 11, 1:43 pm, Dave Peterson 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 -- 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- 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Input box accepting only desired format | Excel Programming | |||
| How do I format a social security number to have no dashes? | Excel Discussion (Misc queries) | |||
| format social security number | Excel Worksheet Functions | |||
| Social Security Number format | Excel Worksheet Functions | |||
| format social security number as XXX-XX-1234 | Excel Programming | |||
Linear Mode
