Excel VBA: Displaying Arrays?

Question

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

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

Below is what I'd like Sheet2 to look like

Can you show a photo of what sheet2 is looking like at the moment? — Scott Craner
– Scott Craner, Commented Dec 2, 2015 at 16:15
It looks like you are going beyond the normal usage of a spreadsheet. What you ask can be easily handled, but over time I think you will wish that you had done this in a database application or on an intranet backed by a database. Just think about years down the road when something needs to be added or combining workbooks to get annual numbers. Just a thought... — Beengie
– Beengie, Commented Dec 2, 2015 at 16:20

Scott Craner · Accepted Answer · 2015-12-03 00:21:27Z

1

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

edited Dec 3, 2015 at 0:21

answered Dec 3, 2015 at 0:12

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Graham Chandler Over a year ago

thanks a lot this worked perfectly. don't know why I was trying to make this so difficult.

Scott Craner Over a year ago

@GrahamChandler So glad I could help. We all get caught in following threads when there is a shorter way.

Collectives™ on Stack Overflow

Excel VBA: Displaying Arrays?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related