PDA

View Full Version : Solved: Referencing Columns in loop



tominga
08-18-2005, 06:58 AM
Help....I'm fairly good at vba but I have come across a request from a co-worker for some help and I can't figure it out.

What I need to do is loop across the columns in a given range and read information into an array.

IE I need to read the value in A1, then B1, then C1... then A2, B2, C2....

I can't figure out how to get the loop to increment by "A,B,C" ect across columns. Incrementing the row portion of the address is easy but I hope someone can help figure out how to make vba "count" across columns since they are not numerical.

Thank you,
Tom

Jacob Hilderbrand
08-18-2005, 07:15 AM
For i = 1 To 2 'Rows
For j = 1 To 3 'Columns
MsgBox Cells(i,j).Value
Next j
Next i

Killian
08-18-2005, 07:19 AM
I'd be inclined to do it a bit like thisDim myRange As Range
Dim c As Long, r As Long
Dim myVals()

Set myRange = Range("F6:H11")
ReDim myVals(1 To myRange.Rows.Count, 1 To myRange.Columns.Count)

For c = 1 To myRange.Columns.Count
For r = 1 To myRange.Rows.Count
myVals(r, c) = myRange(r, c).Value
Next
NextIt gives you the flexibility to work with any range (or you can set the range to the Selection object).

Bob Phillips
08-18-2005, 08:41 AM
Well I'd be inclined to do it like this


Dim myvals

myvals = Range("F6:H11")

Killian
08-18-2005, 11:27 AM
better still...

tominga
08-18-2005, 11:40 AM
Thank you.