Solved: loop through selected cell

[Jacob Edison]

Hi all!

I'm not sure if this is even possible but, i had to ask...:dunno

Lets say i have a few random cells selected (e.g.; a3, a7, b4, c12). Is there a way i can loop through these cells so that i can enter the value of these cells into an array.

eg:

a3 = value1
a7 = value2
b4 = value3
c12 = value4

i need to declare a dynamic array and enter the values in the array.

seeing that these are random cells, is it even possible??:wot

[stanleydgrom]

Jacob Edison,

Welcome to the VBA Express forum.

If in the active worksheet you have (per your example) cells A3, A7, B4, and C12 selected, then run the following macro:

The macro counts the number of cells selected, then creates MyArray, then copies the values in the cells into the array.

To show that it is working, a message box will display the values in the selected cells.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).
Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.




Option Explicit
Sub FillArray()
' stanleydgrom, 05/04/2010
Dim c As Range, MyArray, a As Long
Dim Hold As String
ReDim MyArray(1 To Selection.Count)
For Each c In Selection
a = a + 1
MyArray(a) = c
Hold = Hold & MyArray(a) & ", "
Next c
If Right(Hold, 2) = ", " Then Hold = Left(Hold, Len(Hold) - 2)
MsgBox "MyArray contains: " & Hold
End Sub




Then select some cells in the active worksheet, and run the "FillArray" macro.

[Bob Phillips]

Option Explicit
Sub FillArray()
' stanleydgrom, 05/04/2010
Dim c As Range, MyArray, a As Long
ReDim MyArray(1 To Selection.Count)
For Each c In Selection
a = a + 1
MyArray(a) = c
Hold = Hold & MyArray(a) & ", "
Next c
If Right(Hold, 2) = ", " Then Hold = Left(Hold, Len(Hold) - 2)
MsgBox "MyArray contains: " & Hold
End Sub


.[/B]

You can output the contents of the array without building the string as you go



Sub FillArray()
' stanleydgrom, 05/04/2010
Dim c As Range, MyArray, a As Long
Dim Hold As String
ReDim MyArray(1 To Selection.Count)
For Each c In Selection
a = a + 1
MyArray(a) = c
Next c
MsgBox "MyArray contains: " & Join(MyArray, ",")
End Sub

[Bob Phillips]

One way where the cells are specified



Dim ary As Variant
Dim cell As Range

With Range("A3,A7,B4,C12")

ReDim ary(1 To .Cells.Count)
For Each cell In .Cells

i = i + 1
ary(i) = cell.Value2
Next cell
End With

[stanleydgrom]

xld,

Thank you very much.

More great information for my archives.

[Jacob Edison]

Cool...
Thanks a lot Stanleydgrom and xld...
Donno how it works, but works Perfectly... :bow:
Thanks again....

Will keep asking more questions though...

[Blade Hunter]

It's a shame your range is not contigious otherwise you could do this:


Sub SelectionToArray()
Dim ary As Variant
ary = Application.Transpose(Range("A2:A8").Value)
End Sub

[Jacob Edison]

Thank you Blade Hunter
I'll probably use this too... :)