Arrays
 

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.

Arrays
 

Yes, you could use an array

Dim myArray(1 to 3, 1 to ##)

you can then reference it like so:

myArray(1,1) = "UserID 1"
myArray(2,1) = "Task ID 1"
myArray(3,1) = "Priority 1"
myArray(1,2) = "UserID 2"
myArray(2,2) = "Task ID 2"
myArray(3,2) = "Priority 2"

etc


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.

Arrays
 

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.

Arrays
 

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.

Arrays
 

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.

Arrays
 

That did the trick. I also added this code to remove the blank lines:

If (n.Cells(NewR, 3).Value) = "" Or (n.Cells(NewR, 3).Value) = " " Then
n.Cells(NewR, 3).EntireRow.Delete
NewR = NewR - 1
End If

Thanks for all of your help!


Russell

" wrote:

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.