loop through array number values

Question

the code is breaking at the first if statement. worksheet range error. attempting to loop through myarray. myarray is an array of integers. thanks!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim i As Long, myarray As Variant

    myarray = Array(122, 123)

    For i = LBound(myarray) To UBound(myarray)
        If Range("O" & i) <> 0 Then
            answer = MsgBox("Price Change. Are you sure?", vbYesNo)
        End If

        If answer = vbNo Then
            Range("F" & i).Formula = "=IFERROR(VLOOKUP($B" & i & ",eac_equipment_list!$P:$S,2,FALSE),"""")"
        End If

        If answer = vbYes Then
            Range("O" & i) = "0"

        End If
    Next i
End Sub

LBound(myArray) is 0. So Range("O" & i) becomes Range("O" & 0), which is not a valid address. — BigBen
– BigBen, Commented Oct 31, 2018 at 18:26

urdearboy · Accepted Answer · 2018-10-31 19:37:34Z

Here are a few things to consider:

Make sure the SelectionChange only involves one cell since your code is not set up to handle multiple cells.
You do not need 3 individual IF statements. This has been reduced from 3 IF statements to 2 (where one makes use of the ElseIf)
As discussed in comments, i just shows the index of your array. If you want the value of the index, you need to use myarray(i)
Add Option Explicit to top of your code. Your variable answer needs to be declared

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Long, myarray As Variant, answer As Variant

If Target.Count > 1 Then Exit Sub

myarray = Array(122, 123)

For i = LBound(myarray) To UBound(myarray)
    If Range("O" & myarray(i)) <> 0 Then
        answer = MsgBox("Price Change. Are you sure?", vbYesNo)

        If answer = vbNo Then
            Range("F" & myarray(i)).Formula = "=IFERROR(VLOOKUP($B" & myarray(i) & ",eac_equipment_list!$P:$S,2,FALSE),"""")"
        ElseIf answer = vbYes Then
            Range("O" & myarray(i)) = 0
        End If

    End If
Next i

End Sub

Collectives™ on Stack Overflow

loop through array number values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related