Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Arrays | Excel Discussion (Misc queries) | |||
Working with ranges in arrays... or an introduction to arrays | Excel Programming | |||
Arrays - declaration, adding values to arrays and calculation | Excel Programming | |||
help with arrays | Excel Programming | |||
Arrays | Excel Programming |