CODEWITHSUNDEEP
arraysvbaexcel
Graham Chandler
Graham Chandler

Reputation: 193

Excel VBA: Displaying Arrays?

I'm working on a project for work and I've hit a wall. I'm trying to automate some formatting to speed up a process. On Sheet1, there is a table in the range G2 to W21. The data contained in this table is entered by the user via a userform. After the data is entered, I use this data to drive out Sheet2 is formatted. So far, I've figured out how to handle column G & H of this table the way that I want. I cant figure out how to handle columns I:M and O:W.

Here is the code I've come up with so far:

Dim LineItems As Range, Cell As Range
Dim linearr() As Variant
Dim datasetarr() As Variant
Dim i As Integer
Dim j As Integer
Dim accountnum As Range
Dim accountnumrng As Range

Set LineItems = Sheet1.Range("H2:H21")
Set DataSets = Sheet1.Range("G2:G21")

For Each Cell In LineItems
    If Len(Cell.Value) > 0 Then
        i = i + 1
        ReDim Preserve linearr(1 To i)
        linearr(i) = Cell.Value
    End If
Next Cell

For Each Cell In DataSets
    If Len(Cell.Value) > 0 Then
        j = j + 1
        ReDim Preserve datasetarr(1 To j)
        datasetarr(j) = Cell.Value
    End If
Next Cell

Set accountnumrng = Sheet2.Range("B6:B1000").SpecialCells(xlCellTypeConstants, 23)

For Each accountnum In accountnumrng.Cells

accountnum.Offset(1, 1).Cells(1, 1).Resize(UBound(linearr), 1).Value = Application.Transpose(linearr)
accountnum.Offset(1, 0).Cells(1, 1).Resize(UBound(datasetarr), 1).Value = Application.Transpose(datasetarr)

Next accountnum

here is a picture of the table on Sheet1. Outlined in red are the columns I'm trying to work with

enter image description here

I basically just want to expand on what I've figured out so far. Any help would be greatly appreciated.

Below is a Picture of what Sheet2 looks like right now enter image description here

Below is what I'd like Sheet2 to look like enter image description here

Upvotes: 2

Views: 138

Answers (1)

Scott Craner
Scott Craner

Reputation: 152525

There is no reason to use an array. Ranges are arrays by their nature.

This should do what you want:

Dim accountnum As Range
Dim accountnumrng As Range
Dim lastrow As Long
Dim sze As Long

lastrow = Sheet1.Range("G2").End(xlDown).Row
sze = lastrow - 2 + 1

Set accountnumrng = Sheet2.Range("B6:B1000").SpecialCells(xlCellTypeConstants, 23)

For Each accountnum In accountnumrng.Cells
    accountnum.Offset(1, 8).Resize(sze, 9).Value = Sheet1.Range("O2:W" & lastrow).value
    accountnum.Offset(1, 0).Resize(sze, 7).Value = Sheet1.Range("G2:M" & lastrow).value
Next accountnum

Upvotes: 1

Related Questions