1

I am creating 2 arrays in VBA based on the values present in Column number 16. However some of the values in the column are error values (#N/A). I want to check the value of the column against some condition. If there is error, I want the loop to move over to the next iteration with incremented value of i.

I tried achieving this by writing the following code. The problem is that it is handling the first error value of the column, but is getting stuck on any subsequent error value. For eg. if first error value occurs on row number 50 and the second on 60, the program is not able to handle the error and is coming to a halt at row number 60.

I am quite new to VBA coding, or even coding for that matter. I think this problem arises from the the way VBA handles error and stores it. I tried finding some lead on the web but could not find one. I would appreciate if you could provide an explanation of the problem for conceptual clarity along with a code suggestion.

On Error GoTo caller:
for i = 1 To Worksheets("Sheet1").UsedRange.Rows.Count
If Cells(i, 16).Value = "SP" Then
   arr1(j) = Cells(i, 12).Value
   arr2(j) = Cells(i + 1, 12).Value
   j = j + 1
   ReDim Preserve arr1(j)
   ReDim Preserve arr2(j)
End If
caller:
Next
1
  • Could you include some sample data in a code block and perhaps a screenshot of the error message(s) you're receiving? This will help us replicate and diagnose the issue. Jun 3, 2020 at 18:05

3 Answers 3

0

I don't know why that fails only on the second error value. I created a sample sheet and had the same problem, though, so it's not just your setup. Here's an alternative that only skips the line causing the error. I've tested it on that same sample sheet and it works.

Dim isSP As Boolean
For i = 1 To Worksheets("Sheet1").UsedRange.Rows.Count
    isSP = False
    On Error Resume Next
        isSP = (Cells(i, 16).Value = "SP")
    On Error GoTo 0
    If isSP Then
       arr1(j) = Cells(i, 12).Value
       arr2(j) = Cells(i + 1, 12).Value
       j = j + 1
       ReDim Preserve arr1(j)
       ReDim Preserve arr2(j)
    End If
Next
0

While trying for new ways, I myself stumbled upon a method that works.

On Error GoTo caller:
for i = 1 To Worksheets("Sheet1").UsedRange.Rows.Count
If Cells(i, 16).Value = "SP" Then
arr1(j) = Cells(i, 12).Value
arr2(j) = Cells(i + 1, 12).Value
j = j + 1
ReDim Preserve arr1(j)
ReDim Preserve arr2(j)
End If
res1:
Next
exit sub

caller:
resume res1

end sub
0

I'd recommend more along the lines of:

Dim objCell As Range, intX as Integer, arr1() as Variant, arr2() as Variant
Redim arr1(0):Redim arr2(0)

For Each objCell In Intersect( _
    Worksheets("Sheet1").UsedRange, _
    Worksheets("Sheet1").Columns(16).Cells)

    If Not(WorksheetFunction.IsNA(objCell.Value)) Then

    ' your process
    If objCell.Value="SP" then
        intX=UBound(arr1)
        If intX = 0 then
            if arr1(intX)=0 then intX=-1
        End If
        intX=intX+1
        Redim Preserve arr1(intX)
        Redim Preserve arr2(intX)
        arr1(intX)=objCell.Value
        arr2(intX)=objCell.Offset(0,-4).Value
    End If


    End If
Next objCell

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .