VBA Array Loop
Hello,
Can anyone help me with VBA code that creates an array from a range of cells (A1:E1), loops through the array and performs a function. I already have the function now I need to create the array and loop.
Thank you...
Software/Hardware used:
Excel VBA
Software/Hardware used:
Excel VBA
ASKED:
July 20, 2012 2:18 PM
Answer Wiki:
Last Wiki Answer Submitted:
Be the first to answer this question.
All Answer Wiki Contributors:
Be the first to answer this question.
TomLiotta 
107,995 pts. ,
jasonsss 85 pts. ,
ClaireJosie 40 pts.
107,995 pts. ,
jasonsss 85 pts. ,
ClaireJosie 40 pts. To see all answers submitted to the Answer Wiki: View Answer History.
Why do you need the array ?You can use the cells range pretty much as an array.
So how do I do that?
Here’s a short example. With this code, when you select any range of cells containing more than one cell, it will loop through the range, and will display something for each cell. Take a look:Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim obj_Cell As Range
If Target.Count > 1 Then
For Each obj_Cell In Target.Cells
With obj_Cell
MsgBox “YourFunction(” & .Text & “)”, vbInformation, “Col ” & .Column & ” ” & “Row: ” & .Row
‘Put your function call here
End With
Next
End If
End Sub
I don’t seem to understand how this new editor works. I will post the code alone in my next comment.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim obj_Cell As Range
If Target.Count > 1 Then
For Each obj_Cell In Target.Cells
With obj_Cell
MsgBox “YourFunction(” & .Text & “)”,
vbInformation, “Col ” & .Column & ” ” & “Row: ” & .Row
‘Put your function call here
End With
Next
End If
End Sub