PDA

View Full Version : Solved: Macro showing subscript out of range



Barryj
08-08-2011, 08:09 AM
I am trying to get this macro to copy over column 3 to 2, but it shows subscript out of range, am I missing somthing simple?
Sub test()
Dim r As Range, e
For Each e In Array(Array("SPOT", "SPOT"), Array("TICK", "TICK"), Array("LEAVE", "LEAVE"))
Set r = Columns(3).Find(e(0), , , xlPart, , , False)
If Not r Is Nothing Then
r(3, 2).Value = e(3)
End If
Next
End Sub

Thanks

Bob Phillips
08-08-2011, 08:18 AM
There is no e(3), it is a 2-element array, so there is only e(0) and e(1).

Aflatoon
08-08-2011, 08:18 AM
None of your sub-arrays have four elements, so e(3) will never work.

Barryj
08-08-2011, 08:32 AM
Ok, thanks for putting me on the right track have got it working with the following changes.
Sub test()
Dim r As Range, e
For Each e In Array(Array("SPOT", "SPOT"), Array("TICK", "TICK"), Array("LEAVE", "LEAVE"))
Set r = Columns(3).Find(e(0), , , xlPart, , , False)
If Not r Is Nothing Then
r(1, 0).Value = e(1)
End If
Next
End Sub


Thank you for your assistance.