The problem might be in the code:


for c = 2 to p_maxTask
TaskID (c) = o.Cells(2,c).Value
next c

I mistakenly thought the TaskID's were in row 2. It should read:

for c = 2 to p_maxTask
TaskID (c) = o.Cells(1,c).Value
next c


Dom



Russell wrote:
Thanks Dom,

I believe this is very close to what I'm trying to do. The only problem I'm
having with it is that it is only putting a 0 or 1 in the second column
instead of the Task ID. I'll try tweaking the code to get it to work.

-- Russell


" wrote:

I think this will do it. I assume you have the original data in a
sheet called "Old" and you want the 3-column data in a sheet called
"New"

This is untested, so I'm sure it needs work

----------------------------------------------------------
const p_maxUser as long = ???
const p_maxTask as long = ???



dim UserID (p_maxUser) as Long
dim TaskID (p_maxTask) as Long
dim Data (p_maxUser, p_maxTask) as Long
dim o as excel.worksheet
dim n as excel.worksheet


set o = ActiveWorkbook.Sheets("Old")
set n = ActiveWorkbook.Sheets("New")

for r = 2 to p_maxUser
UserID (r) = o.Cells(r,1).Value
next r

for c = 2 to p_maxTask
TaskID (c) = o.Cells(2,c).Value
next c

for r = 2 to p_maxUser
for c = 2 to p_maxTask
Data (r,c) = o.Cells(r,c).Value
next c
next r


For r = 1 to p_maxUser
for c = 1 to p_maxTask
NewR = NewR + 1
n.Cells(NewR, 1).Value = UserID (r)
n.Cells(NewR, 2).Value = TaskID (c)
n.Cells(NewR, 3).Value = Data (r, c)
next c
next r
-------------------------------------------------------------

Hope this helps (and works!)
Dom



Russell wrote:
I'm sure this is rather simple, however, I'm new to programming and VBA.

I have a spreadsheet where the first column contains the User IDs and the
first row contains the Task IDs. All of the other cells in between contain a
'1' or '2' (priority) where the user has the task. There are many blank cells.

I would like to take this data and put it into 3 columns -- User ID, Task
ID, and Priority. I'm guessing this can be done with an array in VBA, but
that is quite new to me.

Thanks in advance.