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!

score 1 · Accepted Answer · answered Aug 23 '13 at 02:37

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 '13 at 02:37

Doug Glancy

27,214
6
67
115

You bet. Welcome to SO! – Doug Glancy Aug 23 '13 at 02:55

score 0 · Answer 2 · answered Aug 23 '13 at 02:31

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 '13 at 02:31

Grantly

2,546
2
21
31

The problem with this is `Val("43L")` returns the number 43. I'd use the `IsNumeric` function instead. – Doug Glancy Aug 23 '13 at 02:34
1

Yeah that right - much better option, and eliminate the GOTO statements – Grantly Aug 23 '13 at 02:38

VBA Error Handling Multiple times

2 Answers2

Linked