Excel 2007 VBA array question

[mdriver]

Hi,
Hope this topic hasn't been discussed before and if so accept my apologies for bringing it up again.

In my VBA learning quest I have begun learning how to pass ranges to arrays and process the array but haven't gotten to passing back to a range yet.

I noticed something that while looping through the array, writing values to another area on the worksheet that I had to reference the offset from A1. Is this always the case?

I would like to take my range after passing to an array, place the values elsewhere and perform formatting on each column in the range (not the entire column such as "G" but the column limits of the array index). I have the passing part down, the placing values part down (outside of counting how many rows and columns from A1-I would rather begin at the upper left cell of my named range) but got stuck on the relative offset and then formatting part.

For example:
Range P9:S28 is passed to array.
This array is then processed and the values are placed in U9:X28
I would then proceed to format (Horiz. alignment, Vert. Alignment, Number, etc.) columns U9:U28, V9:V28, etc. with different settings.

A little information:
I do have subroutines that name the range via inputbox selection and named range via predefined lists in combobox.
I do also have a subroutine that loops through each cell of that named range, offsets the value and performs the formatting.
Not too bad but see it getting slow with lengthy data lists. My testing with the array sped things up considerably as I also have read as well.
Being a newbie I likely went the slow route first but am learning better ways of coding.

Hope all of this makes sense.

Thanks,
Mark Driver

 

[pgc01]

Hi Mark

Not sure I understood exactly, but see if this example helps.

After processing the array, it is written to U9:X28 and each of the 4 columns of that range gets a different format:

Sub Main()
Dim rIn As Range

Set rIn = Range("P9:S28")
Test rIn.Value
End Sub

Sub Test(vArr As Variant)
Dim rOut As Range
Dim j As Long

Set rOut = Range("U9:X28")

' process the values of the array
vArr(1, 1) = "Hi!"
'...

' copy the values to the output range
rOut = vArr

' format the columns of rOut
For j = 1 To 4
    With rOut.Columns(j)
    
        Select Case j
            Case 1:
                .Interior.Color = vbGreen
                .HorizontalAlignment = xlCenter
            
            Case 2:
                .Interior.Color = vbRed
            
            Case 3:
                .Interior.Color = vbBlue
                .NumberFormat = "0.00"
            
            Case 4:
                .Interior.Color = vbMagenta
        
        End Select
    End With
Next j
End Sub

 

[mdriver]

Hi pgc01,
I'm sorry it's hard to convey everything.

I will study your example and reply soon.

Thanks again for your input.

Mark Driver