Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jul 2004
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am attempting to do a text to column action and then convert a value. My files have different values that I am searching on:

    When I do a search on the first string it should fail since the value is "POINT( ", not "POINT ( ".

    I recieve an error: "Run-time error '91': Object variable or With Block Variable not set", but the code should have then moved on to the label "WrongPoint1" and did not. This happens after I run through this script on two different files.

    These are the varying values in a column that I am trying to find then convert.
    "POINT ( ",
    "POINT( ",
    "POLYGON( ",

    Any help would be appreciated.

    This is my script:
    [codebox]
    Sub CCConvert()

    'An error will occur on the find if the file does not have a successful find,
    'so I skip this error and go to the next script until I reach the right
    'value to find in the file for POINT or POLYGON.

    If Err.Number = 91 Then GoTo WrongPoint1

    Cells.Find(What:="POINT ( ", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate

    Selection.EntireColumn.Select
    Selection.Copy
    ActiveWindow.SmallScroll ToRight:=106
    Range("EI1").Select
    ActiveSheet.Paste
    Columns("EI:EI").Select
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("EI1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=True, Other:=True, FieldInfo:= _
    Array(Array(1, 9), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:= _
    True
    'Have to include a separate text to columns for text files since there is
    'an extra ) in the Point column.
    Columns("EK:EK").Select
    Selection.TextToColumns Destination:=Range("EK1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :=")", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Range("EL2").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]/30.48006096"
    Range("EL2").Select
    Selection.AutoFill Destination:=Range("EL2:EL2887")
    Range("EL2:EL2887").Select
    Range("EM2").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]/30.48006096"
    Range("EM2").Select
    Selection.AutoFill Destination:=Range("EM2:EM2887")
    Range("EM2:EM2887").Select
    Columns("EL:EM").Select
    Selection.Copy
    ActiveWindow.LargeScroll ToRight:=-7
    Columns("A:B").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("A1").Value = "X Coordinate"
    Range("B1").Value = "Y Coordinate"

    'Filter On
    Rows("1:1").Select
    Selection.AutoFilter

    'Code skips here if file has a space in front of POINT, so I have to repeat the code
    'but do a Find on a space in POINT. Some files have a space in front of Point and
    'some don't.

    WrongPoint1:

    If Err.Number = 91 Then GoTo NotaPOINT
    Cells.Find(What:="POINT( ", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    Selection.EntireColumn.Select
    Selection.Copy
    ActiveWindow.SmallScroll ToRight:=106
    Range("EI1").Select
    ActiveSheet.Paste
    Columns("EI:EI").Select
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("EI1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=True, Other:=True, FieldInfo:= _
    Array(Array(1, 9), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:= _
    True
    Range("EL2").Select
    ActiveCell.FormulaR1C1 = "=RC[-3]/30.48006096"
    Range("EL2").Select
    Selection.AutoFill Destination:=Range("EL2:EL2887")
    Range("EL2:EL2887").Select
    Range("EM2").Select
    ActiveCell.FormulaR1C1 = "=RC[-3]/30.48006096"
    Range("EM2").Select
    Selection.AutoFill Destination:=Range("EM2:EM2887")
    Range("EM2:EM2887").Select
    Columns("EL:EM").Select
    Selection.Copy
    ActiveWindow.LargeScroll ToRight:=-7
    Columns("A:B").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("A1").Value = "X Coordinate"
    Range("B1").Value = "Y Coordinate"




    NotaPOINT:

    On Error GoTo ExitM

    Cells.Find(What:="POLYGON( ", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    Selection.EntireColumn.Select
    Selection.Copy
    ActiveWindow.SmallScroll ToRight:=106
    Range("EI1").Select
    ActiveSheet.Paste
    Columns("EI:EI").Select
    Columns("EI:EI").Select
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("EI1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
    Semicolon:=False, Comma:=True, Space:=True, Other:=True, OtherChar:= _
    "", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), _
    Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1)), _
    TrailingMinusNumbers:=True
    Columns("EI:EI").Select
    Selection.Delete Shift:=xlToLeft
    Columns("EK:EL").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("EK2").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]/30.48006096"
    Range("EK2").Select
    Selection.AutoFill Destination:=Range("EK2:EK2887")
    Range("EL2:EL2887").Select
    Range("EL2").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]/30.48006096"
    Range("EL2").Select
    Selection.AutoFill Destination:=Range("EL2:EL2887")
    Range("EL2:EL2887").Select
    Columns("EK:EL").Select
    Selection.Copy
    ActiveWindow.LargeScroll ToRight:=-7
    Columns("A:B").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("A1").Value = "X Coordinate"
    Range("B1").Value = "Y Coordinate"


    ExitM:
    MsgBox "No Points in this file"
    'Filter On
    Rows("1:1").Select
    Selection.AutoFilter
    End Sub[/codebox]

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The line

    If Err.Number = 91 Then GoTo WrongPoint1

    near the beginning does *not* activate an error handler. It merely checks whether an error has occurred at that point (pun intended) in the code, which will never occur since it is the very first line of code in the procedure.

    To activate an error handler, use

    On Error GoTo <labelname>

    and in the code after the label inspect the error number, or use

    On Error Resume Next

    and inspect the error number immediately below the instruction that could cause an error.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •