Prev: How to code it in macro?
Next: Excel VBA Populatig UserFrom Contols as Design Time.
From: Bythsx-Addagio on 20 Mar 2010 20:53
Hello,
I am hoping to work out a method to transform a data table into a running
list.
The table has identifiers in column A, and product weights in a dynamic
number of columns to the right. i.e. Cols B-M. I would like to manipulate
the table into a running list of three columns

Col A = Value of ID @ row(i)
Col B = Product name [value of Cols B-M row(1)]
Col C = Value of weight @ row(i)

Col A | Col B | Col C
A2 B1 B2
A3 B1 B3
A4 B1 B4
A2 C1 C2
A3 C1 C3
A4 C1 C4
A2 D1 A2 ... etc.

I was thinking a 2D array might be the correct approach. Would there be a
better solution? And if the Array is the way to go, where would be the best
place to start?

Thanks in advance,
From: joel on 20 Mar 2010 23:58

I like moving this type data from one worksheet to another worksheet
like the code below. The variable names should explain what the code
does.


Sub MakeRunning()

Set Srcsht = Sheets("sheet1")
Set DestSht = Sheets("sheet2")

DestRowCount = 1
With Srcsht
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column

For ColCount = 2 To LastCol
ColHeader = .Cells(1, ColCount)
For RowCount = 2 To LastRow
RowHeader = .Range("A" & RowCount)
Data = .Cells(RowCount, ColCount)

With DestSht
.Range("A" & DestRowCount) = RowHeader
.Range("B" & DestRowCount) = ColHeader
.Range("C" & DestRowCount) = Data
DestRowCount = DestRowCount + 1
End With

Next RowCount

Next ColCount

End With

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=189254

http://www.thecodecage.com/forumz/chat.php

 | 
Pages: 1
Prev: How to code it in macro?
Next: Excel VBA Populatig UserFrom Contols as Design Time.