IF(ISERROR) in an array

[tiredofit]

I have these formulae in cells:

=IF(ISERROR(A1/B1),0,A1/B1)

=IF(ISERROR(C1/D1),0,C1/D1)

How can I create this in a VBA array?

For Example:

Dim DataArray() As Variant

DataArray = Cells(1,1).CurrentRegion.Value

Dim MyArray(1 To 10, 1 To 2) As Variant

Dim Counter As Integer

On Error GoTo ErrHandler

For Counter = 1 To 10

    MyArray(Counter, 1) = DataArray(Counter, 1) / DataArray(Counter, 2)
    MyArray(Counter, 2) = DataArray(Counter, 3) / DataArray(Counter, 4)

Next Counter

Exit Sub

ErrHandler:

MyArray(Counter, 1) = 0
MyArray(Counter, 2) = 0

Resume Next

If only B1 OR D1 is zero, it will error out but how can my array know which one errors?

Thanks

 

[cerfani]

a division will error if the denominator is 0 or if a value is not numeric... i would think those are the only situations. So you can check the values before the division and if they are not numbers or denominator > 0 then do something else. I kinda don't follow what you mean by "how will my array know" put a value of -1 for errors or something, it is a variant array so put a string value that says "error" The logic is up to you.

 

[Marcelo Branco]

One way

Dim DataArray() As Variant

DataArray = Cells(1, 1).CurrentRegion.Value
Dim MyArray(1 To 10, 1 To 1) As Variant
Dim Counter As Integer
For Counter = 1 To 10
    On Error Resume Next
    MyArray(Counter, 1) = DataArray(Counter, 4) / DataArray(Counter, 5)
    If Err.Number <> 0 Then
        MyArray(Counter, 1) = 0
        On Error GoTo 0
    End If
Next Counter
'Just to check - optional
Range("F1:F10") = MyArray

M.

 

[tiredofit]

One way

Dim DataArray() As Variant

DataArray = Cells(1, 1).CurrentRegion.Value
Dim MyArray(1 To 10, 1 To 1) As Variant
Dim Counter As Integer
For Counter = 1 To 10
    On Error Resume Next
    MyArray(Counter, 1) = DataArray(Counter, 4) / DataArray(Counter, 5)
    If Err.Number <> 0 Then
        MyArray(Counter, 1) = 0
        On Error GoTo 0
    End If
Next Counter
'Just to check - optional
Range("F1:F10") = MyArray

M.

Thanks but would I have to do TWO loops, one to check B1 <> 0 and another for D1 <> 0?

 

[Marcelo Branco]

Thanks but would I have to do TWO loops, one to check B1 <> 0 and another for D1 <> 0?

You edited the original post. Wait for a new version

M.

 

[Marcelo Branco]

Try

Sub aTest()
    Dim DataArray() As Variant, Counter As Integer, MyArray(1 To 10, 1 To 2) As Variant

    DataArray = Cells(1, 1).CurrentRegion.Value

    For Counter = 1 To 10
        On Error Resume Next
        MyArray(Counter, 1) = DataArray(Counter, 1) / DataArray(Counter, 2)
        If Err.Number <> 0 Then MyArray(Counter, 1) = 0
        On Error GoTo 0
        
        On Error Resume Next
        MyArray(Counter, 2) = DataArray(Counter, 3) / DataArray(Counter, 4)
        If Err.Number <> 0 Then MyArray(Counter, 2) = 0
        On Error GoTo 0
    Next Counter
    
    'Just to check
    Range("G1:H10") = MyArray
End Sub

M.

 

[tiredofit]

Thanks

 

[tiredofit]

Try

Sub aTest()
    Dim DataArray() As Variant, Counter As Integer, MyArray(1 To 10, 1 To 2) As Variant

    DataArray = Cells(1, 1).CurrentRegion.Value

    For Counter = 1 To 10
        On Error Resume Next
        MyArray(Counter, 1) = DataArray(Counter, 1) / DataArray(Counter, 2)
        If Err.Number <> 0 Then MyArray(Counter, 1) = 0
        On Error GoTo 0
        
        On Error Resume Next
        MyArray(Counter, 2) = DataArray(Counter, 3) / DataArray(Counter, 4)
        If Err.Number <> 0 Then MyArray(Counter, 2) = 0
        On Error GoTo 0
    Next Counter
    
    'Just to check
    Range("G1:H10") = MyArray
End Sub

M.

Thanks, your suggestion works well for this example.

However, if I had a different condition, like:

If MyArray(Counter, 1) > 10 Then

and MyArray(Counter, 1) was NOT numeric but a letter, for some reason it does NOT error out!

a division will error if the denominator is 0 or if a value is not numeric... i would think those are the only situations. So you can check the values before the division and if they are not numbers or denominator > 0 then do something else. I kinda don't follow what you mean by "how will my array know" put a value of -1 for errors or something, it is a variant array so put a string value that says "error" The logic is up to you.

Therefore I used Cerfani's suggestion to check for numeric before proceeding.

 

[Marcelo Branco]

Don't understand what you are saying.

All the values in MyArray are the results of a division
MyArray(Counter, 1) = DataArray(Counter, 1) / DataArray(Counter, 2)
MyArray(Counter, 2) = DataArray(Counter, 3) / DataArray(Counter,4)

They cannot ever be a letter.

M.

 

[tiredofit]

Don't understand what you are saying.

All the values in MyArray are the results of a division
MyArray(Counter, 1) = DataArray(Counter, 1) / DataArray(Counter, 2)
MyArray(Counter, 2) = DataArray(Counter, 3) / DataArray(Counter,4)

They cannot ever be a letter.

M.

Sorry but if my original formula was different, say:

=IF(ISERROR(IF(A2>Z100,E2,E2/F2),0,IF(A2>Z100,E2,E2/F2))

then if A2 was a letter, say p but Z100 is the value 1000, it does NOT error.