CODEWITHSUNDEEP
excelvbaerror-handling
jmdatasci
jmdatasci

Reputation: 111

Using a VBA Try/Except Equivalent for If/Else

I am trying to run through some spreadsheet range and use a try/except in order to build an if/else statement. The reason I am doing this is because IsNumeric() is not working for me so I am trying to do something like this (try except formatting from python)

Dim Temp as Integer
Dim Myrange as Range
Dim Myrow as Range
Set Myrange = Range("A1","A1000")
For Each Myrow in Myrange.Row
  If IsEmpty(Range("A" & Myrow.Row)) Then  
    Exit For 'To escape the loop at the end of the filled cells
  Else
    Try:
      Temp = (Myrow.Value() - 0) 'This causes a #VALUE! error when the Myrow.Value is not a number.
    Except:
        Range("B" & Myrow.Row).Value = Temp 'this sets the value of the rightmost cell to whatever current value of Temp is.

I have also tried some other error catching but can't seem to get it in VBA. 

For Each Myrow In Myrange.Rows
       If IsEmpty(Range("A" & Myrow.Row)) Then
         Exit For
       Else
           On Error Resume Next
           Temp = Myrow.Value() - 0
           If Err.Number = 0 Then
               Range("A" & Myrow.Row).Value = ""
           ElseIf Err.Number <> 0 Then
               Range("B" & Myrow.Row) = Temp
           End If
       End If
   Next Myrow

I am really just looking to run down the list, see the first number, set value of B0:Bn1 = Temp, when An is hit (new number), The value of Temp changes to temp2 and then cells Bn1+1 -> Bn2-1 is temp2 until a new number is found etc.

in the worksheet I can do it fine with dragging down formula =(A1-0) to see the error message for those that are not numeric but for some reason I can't code it.

Solved this using advice of @MathieuGuindon by using variant type and testing isnumeric on that. Solution code: 

    Dim Myrange As Range
    Dim Myrow As Range
    Dim Temp As Variant
    Dim NextTemp As Variant

    Set Myrange = Selection
    For Each Myrow In Myrange.Rows
        NextTemp = Range("A" & Myrow.Row).Value
        If IsEmpty(Range("A" & Myrow.Row)) Then
          Exit For
        ElseIf IsNumeric(NextTemp) Then
            Temp = NextTemp
            Range("A" & Myrow.Row).Value = ""
        Else
            Range("B" & Myrow.Row).Value = Temp
                End If
    Next Myrow

Upvotes: 0

Views: 3142

Answers (3)

user11494081
user11494081

Reputation: 1

While the first example contains Try: and Except: as labels, they provide no error control. Try/Except are error control methods, not .

It's unclear whether you might have text that looks like numbers in column A. If the Temp = (Myrow.Value() - 0) is only meant to determine whether the value in column A is a number and not used as a conversion then SpecialCells can quickly find the numbers in column A.

dim rng as range

on error resume next
'locate typed numbers in column A
set rng = Range("A:A").SpecialCells(xlCellTypeConstants, xlNumbers)
on error goto 0

If not rng is nothing then
    rng = vbNullString
End If

on error resume next
'locate text values in column A
set rng = Range("A:A").SpecialCells(xlCellTypeConstants, xlTextValues)
on error goto 0

If not rng is nothing then
    rng.Offset(0, 1) = rng.Value
End If

You can also use xlCellTypeFormulas to return numbers or text returned by formulas.

Upvotes: 0

SJR
SJR

Reputation: 23081

A bit of simplification, and picking up on Mathieu's comments, try this. Not sure what you're doing though so may no be quite right.

Sub x()

Dim Temp As Variant
Dim Myrange As Range
Dim Myrow As Range

Set Myrange = Range("A1", "A1000")

For Each Myrow In Myrange
    If Not IsEmpty(Myrow) Then
        Temp = Myrow.Value - 0
            If IsNumeric(Temp) Then
               Myrow.Value = vbNullString
            Else
                Myrow.Offset(, 1).Value = Temp
            End If
    End If
Next Myrow

End Sub

Upvotes: 1

Zack
Zack

Reputation: 2341

One way is to have a dedicated error handler at the end of your sub, and check the error code (13 for Type Mismatch):

Option Explicit

Public Sub EnumerateValues()
    On Error GoTo err_handle

    Dim Temp As Integer
    Dim Myrange As Range
    Dim Myrow As Range
    Dim myNumber As Double ' Int? Long?

    Set Myrange = Range("A1", "A1000")

    For Each Myrow In Myrange.Rows
        If IsEmpty(Range("A" & Myrow.Row)) Then
            Exit For ' to escape loop at end of filled cells
        Else
            myNumber = CDbl(Myrow.Value())
            Debug.Print myNumber
        End If
' use label, since VBA doesn't support Continue in loop.
loop_continue:
    Next Myrow

exit_me:
    Exit Sub

err_handle:
    Select Case Err.Number
        Case 13 ' Type Mismatch
            GoTo loop_continue
        Case Else
            MsgBox Err.Description, vbOKOnly + vbCritical, Err.Number
            GoTo exit_me
    End Select
End Sub

This way, if we encounter a value for which CDbl (or the equivalent function) fails, we just continue on to the next row.

Upvotes: 0

Related Questions