VBA Error Handling Multiple times

Question

I have an issue. I have values in a workbook that are getting read into an array. The values come from an XML list so sometimes can be numbers or text, and if they are numbers in text format ("1" for example), they need to be converted to number format, so I multiply them by 1. If the value is text "LS" for example, I am trying to use an error handler to keep the value as "LS".

I have developed the code below: It works once, but the next time I use a similar method (with 'Dummy2') the code it produces a 'Type Mismatch' error.

    On Error GoTo Dummy1
    For i = 1 To nrows1 - 1
        If (Worksheets("Import").Cells(i + 1, 26) * 1 = "") Then
Dummy1:

            Table1(i, 1) = Worksheets("Import").Cells(i + 1, 26)
        Else
            Table1(i, 1) = Worksheets("Import").Cells(i + 1, 26) * 1
        End If
    Next
    On Error GoTo 0

I also tried Clearing Err after the above code with no success.

Please help!

Doug Glancy · Accepted Answer · 2013-08-23 02:37:33Z

1

You can use the IsNumeric function:

Sub test3()
Dim Test As Variant
Dim i As Long

For i = 1 To nrows1 - 1
Test = Worksheets("Import").Cells(i + 1, 26)
    If IsNumeric(Test) Then
        Table1(i, 1) = Test * 1
    Else
        Table1(i, 1) = Test
    End If
Next
End Sub

answered Aug 23, 2013 at 2:37

Doug Glancy

27.5k6 gold badges72 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Doug Glancy Over a year ago

You bet. Welcome to SO!

Grantly · Accepted Answer · 2013-08-23 02:31:29Z

0

use VAL(Worksheets("Import").Cells(i + 1, 26)) to determine if it is a Number or not. VAL will give you 0 for "LS".

You could try TryParse functions - but not sure if its available in your version of VBA

answered Aug 23, 2013 at 2:31

Grantly

2,5642 gold badges23 silver badges32 bronze badges

2 Comments

Doug Glancy Over a year ago

The problem with this is Val("43L") returns the number 43. I'd use the IsNumeric function instead.

Grantly Over a year ago

Yeah that right - much better option, and eliminate the GOTO statements

Collectives™ on Stack Overflow

VBA Error Handling Multiple times

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related