using arrays to transpose data
 

I have a spread sheet with 4 decriptive fields plus 12 fields that hold a
value for each calendar month. I want to convert this format to a database
table with 6 fields, the fifth to hold the month number and the sixth the
month value. I have a routine coded that works, but I think it could be
better done by populating an array (looping through the months), then copying
the arrray to another sheet/range for import into Access.

Thoughts?

DM

using arrays to transpose data
 

Option Explicit
Sub ABCD()
Dim vArr() As Variant
Dim vArr1 As Variant
Dim rng As Range, i As Long, j As Long
Dim ii As Long, jj As Long
With Worksheets("Sheet1")
Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With
vArr1 = rng.Resize(, 16).Value
ReDim vArr(1 To rng.Rows.Count * 12, 1 To 6)
For ii = 1 To rng.Rows.Count
i = (ii - 1) * 12 + 1
jj = 4
For j = i To i + 11
jj = jj + 1
vArr(j, 1) = vArr1(ii, 1)
vArr(j, 2) = vArr1(ii, 2)
vArr(j, 3) = vArr1(ii, 3)
vArr(j, 4) = vArr1(ii, 4)
vArr(j, 5) = jj - 4
vArr(j, 6) = vArr1(ii, jj)
Next
Next
Worksheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Range("A1:A4").Value = rng.Parent.Range("A1:A4").Value
ActiveSheet.Range("A5:A6").Value = Array("Month", "Value")
ActiveSheet.Range("A2").Resize(UBound(vArr), 6).Value = vArr
End Sub

--
Regards,
Tom Ogilvy

"Dick Minter" wrote in message
...
I have a spread sheet with 4 decriptive fields plus 12 fields that hold a
value for each calendar month. I want to convert this format to a
database
table with 6 fields, the fifth to hold the month number and the sixth the
month value. I have a routine coded that works, but I think it could be
better done by populating an array (looping through the months), then
copying
the arrray to another sheet/range for import into Access.

Thoughts?

DM

using arrays to transpose data
 

Had a problem in my transfer header portion of the code. here is a
correction

Option Explicit
Sub ABCD()
Dim vArr() As Variant
Dim vArr1 As Variant
Dim rng As Range, i As Long, j As Long
Dim ii As Long, jj As Long
With Worksheets("Sheet1")
Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With
vArr1 = rng.Resize(, 16).Value
ReDim vArr(1 To rng.Rows.Count * 12, 1 To 6)
For ii = 1 To rng.Rows.Count
i = (ii - 1) * 12 + 1
jj = 4
For j = i To i + 11
jj = jj + 1
vArr(j, 1) = vArr1(ii, 1)
vArr(j, 2) = vArr1(ii, 2)
vArr(j, 3) = vArr1(ii, 3)
vArr(j, 4) = vArr1(ii, 4)
vArr(j, 5) = jj - 4
vArr(j, 6) = vArr1(ii, jj)
Next
Next
Worksheets.Add After:=Worksheets(Worksheets.Count)
' corrections here.
ActiveSheet.Range("A1:D1").Value = rng.Parent.Range("A1:D1").Value
ActiveSheet.Range("E1:F1").Value = Array("Month", "Value")
ActiveSheet.Range("A2").Resize(UBound(vArr), 6).Value = vArr
End Sub

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
Option Explicit
Sub ABCD()
Dim vArr() As Variant
Dim vArr1 As Variant
Dim rng As Range, i As Long, j As Long
Dim ii As Long, jj As Long
With Worksheets("Sheet1")
Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With
vArr1 = rng.Resize(, 16).Value
ReDim vArr(1 To rng.Rows.Count * 12, 1 To 6)
For ii = 1 To rng.Rows.Count
i = (ii - 1) * 12 + 1
jj = 4
For j = i To i + 11
jj = jj + 1
vArr(j, 1) = vArr1(ii, 1)
vArr(j, 2) = vArr1(ii, 2)
vArr(j, 3) = vArr1(ii, 3)
vArr(j, 4) = vArr1(ii, 4)
vArr(j, 5) = jj - 4
vArr(j, 6) = vArr1(ii, jj)
Next
Next
Worksheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Range("A1:A4").Value = rng.Parent.Range("A1:A4").Value
ActiveSheet.Range("A5:A6").Value = Array("Month", "Value")
ActiveSheet.Range("A2").Resize(UBound(vArr), 6).Value = vArr
End Sub

--
Regards,
Tom Ogilvy

"Dick Minter" wrote in message
...
I have a spread sheet with 4 decriptive fields plus 12 fields that hold
a
value for each calendar month. I want to convert this format to a
database
table with 6 fields, the fifth to hold the month number and the sixth
the
month value. I have a routine coded that works, but I think it could be
better done by populating an array (looping through the months), then
copying
the arrray to another sheet/range for import into Access.

Thoughts?

DM