CODEWITHSUNDEEP
arraysvbaloops
GlenAllan
GlenAllan

Reputation: 1

loop through array number values

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

Upvotes: 0

Views: 48

Answers (1)

urdearboy
urdearboy

Reputation: 14590

Here are a few things to consider:

  1. Make sure the SelectionChange only involves one cell since your code is not set up to handle multiple cells.
  2. 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)
  3. 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)
  4. 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

Upvotes: 1

Related Questions