David Condit
New Member
- Joined
- Dec 26, 2012
- Messages
- 6
Hi, all! This is my first post on your forum. While searching for misc VBA answers on google I've often been directed to this site, so I opted to register; I hope one of you can help me solve my current problem.
Is there a better way to fill a 2D array than what I have coded below? It seems I should be able to fill a 2D array from a multi-row single-column range without needing an If...Then...Else or Select Case statement. Any help would be appreciated!
Is there a better way to fill a 2D array than what I have coded below? It seems I should be able to fill a 2D array from a multi-row single-column range without needing an If...Then...Else or Select Case statement. Any help would be appreciated!
Code:
Sub ColorsArrayTest() ' Fills a 2D array from a 1D named range.
Dim Rng As Range
Dim TempArr As Variant
Dim r As Long, c As Long, MaxRow As Long
Dim msg As String
' Assign Rng to a R6C1 named range, "Colors."
Set Rng = Worksheets("Sheet1").Range("Colors")
' Fill a 2D Array (6 rows, 2 columns) with
' Rng's value property in column 1 and Rng's
' address property in column.
'
' Is there a better way to do this??? Surely there's
' a better way to fill a 2d array besides validating
' the array element with an If or Select Case statement
' after each loop... help me!
MaxRow = Rng.Rows.Count
ReDim TempArr(1 To MaxRow, 1 To 2)
For r = 1 To MaxRow
For c = 1 To 2
If c = 1 Then
TempArr(r, c) = Rng(r, c).Value
msg = msg & TempArr(r, c) & vbTab
Else
TempArr(r, c) = Rng(r, 1).Address
msg = msg & TempArr(r, c) & vbCrLf
End If
Next c
Next r
' Spits out the Colors named range values
' and its values' address properties in
' two columns.
MsgBox msg
End Sub