Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Then and Copy/Paste
Good morning,
I am really new at programming in excel -- I've recorded a few macros, but now I am needing functionality that is going to require me to program something. I was hoping to find some help... I've got a worksheet in a workbook that contains column values that are numbered like this: 1. 1.1 1.2 1.3 1.4 (ect) The column to the right of this column is blank, and the column next to the blank column contains task type information. For the tasks numbered 1. (with no subnumber), the data in the task column is essentially a heading (if you are thinking in terms of project management, that information is a summary task). I need to have a function that will run through my column (it is column b) and any time it hits a summary number 1. , 2. , 3. , 4. (ect) I need it to look to the right 2 columns and pull that summary data and do 2 things with it: 1. Copy and paste it onto another worksheet (I need the number, the blank column, and the summary task -- so three columns). 2. Copy the summary task data and put it in the blank column for the non summary tasks. It kind of looks like this right now: Area AreaName Task 1. SITE SETUP 1.1 Plans, permits, and special engineering 1.2 Site prep - removal of topsoil 1.3 Job shack and other rentals 2. DEMO 2.1 Remove exsisting hot tub 2.2 Demo patio and haul 3. POOL 3.1 Supply new pool This is what I want it to look like: Area AreaName Task 1. SITE SETUP 1.1 SITE SETUP Plans, permits, and special engineering 1.2 SITE SETUP Site prep - removal of topsoil 1.3 SITE SETUP Job shack and other rentals 2. DEMO 2.1 DEMO Remove exsisting hot tub 2.2 DEMO Demo patio and haul 3. POOL 3.1 POOL Supply new pool Does that make sense? Thanks in advance - this will make my life SOO much easier! -AshleyRose |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Then and Copy/Paste
Are the number in the first column numbers or text? thsi wil work on numbers
If they are not numbers then modify as follows: from If Cells(RowCount, "A").Value = _ Int(Cells(RowCount, "A").Value) Then to If Val(Cells(RowCount, "A").Value) = _ Int(Val(Cells(RowCount, "A").Value)) Then Sub test() LastRow = Cells(Rows.Count, "A").End(xlUp).Row For RowCount = 2 To LastRow If Cells(RowCount, "A").Value = _ Int(Cells(RowCount, "A").Value) Then Cells(RowCount, "C").Cut _ Destination:=Cells(RowCount, "B") End If Next RowCount End Sub "ARBrock" wrote: Good morning, I am really new at programming in excel -- I've recorded a few macros, but now I am needing functionality that is going to require me to program something. I was hoping to find some help... I've got a worksheet in a workbook that contains column values that are numbered like this: 1. 1.1 1.2 1.3 1.4 (ect) The column to the right of this column is blank, and the column next to the blank column contains task type information. For the tasks numbered 1. (with no subnumber), the data in the task column is essentially a heading (if you are thinking in terms of project management, that information is a summary task). I need to have a function that will run through my column (it is column b) and any time it hits a summary number 1. , 2. , 3. , 4. (ect) I need it to look to the right 2 columns and pull that summary data and do 2 things with it: 1. Copy and paste it onto another worksheet (I need the number, the blank column, and the summary task -- so three columns). 2. Copy the summary task data and put it in the blank column for the non summary tasks. It kind of looks like this right now: Area AreaName Task 1. SITE SETUP 1.1 Plans, permits, and special engineering 1.2 Site prep - removal of topsoil 1.3 Job shack and other rentals 2. DEMO 2.1 Remove exsisting hot tub 2.2 Demo patio and haul 3. POOL 3.1 Supply new pool This is what I want it to look like: Area AreaName Task 1. SITE SETUP 1.1 SITE SETUP Plans, permits, and special engineering 1.2 SITE SETUP Site prep - removal of topsoil 1.3 SITE SETUP Job shack and other rentals 2. DEMO 2.1 DEMO Remove exsisting hot tub 2.2 DEMO Demo patio and haul 3. POOL 3.1 POOL Supply new pool Does that make sense? Thanks in advance - this will make my life SOO much easier! -AshleyRose |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Then and Copy/Paste
Wow Joel! Your response was very quick!
I tried this code and what it did was move the summary task over one column -- which was helpful... but it didn't fill the name down the nonblank cells to the next cell in the column: This is what I started with: Area AreaName Task 1. SITE SETUP 1.1 Plans, permits, and special engineering 1.2 Site prep - removal of topsoil 1.3 Job shack and other rentals 2. DEMO 2.1 Remove exsisting hot tub 2.2 Demo patio and haul 3. POOL 3.1 Supply new pool When I run the code you gave me I end up with: Area AreaName Task 1. SITE SETUP 1.1 Plans, permits, and special engineering 1.2 Site prep - removal of topsoil 1.3 Job shack and other rentals 2. DEMO 2.1 Remove exsisting hot tub 2.2 Demo patio and haul 3. POOL 3.1 Supply new pool Now... If I can get something that will go through column B and notice that there is a cell that has something in it and then copy/paste or autofill it down to the next cell that has something in it... and then it will copy that cell and copy/paste or autofill it down.... This is what I want my end goal to look like: Area AreaName Task 1. SITE SETUP 1.1 SITE SETUP Plans, permits, and special engineering 1.2 SITE SETUP Site prep - removal of topsoil 1.3 SITE SETUP Job shack and other rentals 2. DEMO 2.1 DEMO Remove exsisting hot tub 2.2 DEMO Demo patio and haul 3. POOL 3.1 POOL Supply new pool If I could get the worksheet to here, I would be ever so grateful! -AR "Joel" wrote: Are the number in the first column numbers or text? thsi wil work on numbers If they are not numbers then modify as follows: from If Cells(RowCount, "A").Value = _ Int(Cells(RowCount, "A").Value) Then to If Val(Cells(RowCount, "A").Value) = _ Int(Val(Cells(RowCount, "A").Value)) Then Sub test() LastRow = Cells(Rows.Count, "A").End(xlUp).Row For RowCount = 2 To LastRow If Cells(RowCount, "A").Value = _ Int(Cells(RowCount, "A").Value) Then Cells(RowCount, "C").Cut _ Destination:=Cells(RowCount, "B") End If Next RowCount End Sub "ARBrock" wrote: Good morning, I am really new at programming in excel -- I've recorded a few macros, but now I am needing functionality that is going to require me to program something. I was hoping to find some help... I've got a worksheet in a workbook that contains column values that are numbered like this: 1. 1.1 1.2 1.3 1.4 (ect) The column to the right of this column is blank, and the column next to the blank column contains task type information. For the tasks numbered 1. (with no subnumber), the data in the task column is essentially a heading (if you are thinking in terms of project management, that information is a summary task). I need to have a function that will run through my column (it is column b) and any time it hits a summary number 1. , 2. , 3. , 4. (ect) I need it to look to the right 2 columns and pull that summary data and do 2 things with it: 1. Copy and paste it onto another worksheet (I need the number, the blank column, and the summary task -- so three columns). 2. Copy the summary task data and put it in the blank column for the non summary tasks. It kind of looks like this right now: Area AreaName Task 1. SITE SETUP 1.1 Plans, permits, and special engineering 1.2 Site prep - removal of topsoil 1.3 Job shack and other rentals 2. DEMO 2.1 Remove exsisting hot tub 2.2 Demo patio and haul 3. POOL 3.1 Supply new pool This is what I want it to look like: Area AreaName Task 1. SITE SETUP 1.1 SITE SETUP Plans, permits, and special engineering 1.2 SITE SETUP Site prep - removal of topsoil 1.3 SITE SETUP Job shack and other rentals 2. DEMO 2.1 DEMO Remove exsisting hot tub 2.2 DEMO Demo patio and haul 3. POOL 3.1 POOL Supply new pool Does that make sense? Thanks in advance - this will make my life SOO much easier! -AshleyRose |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Then and Copy/Paste
This should work.
Sub test() AreaName = "" LastRow = Cells(Rows.Count, "A").End(xlUp).Row For RowCount = 2 To LastRow If Cells(RowCount, "A").Value = _ Int(Cells(RowCount, "A").Value) Then AreaName = Cells(RowCount, "C").Value Else Cells(RowCount, "B").Value = AreaName End If Next RowCount End Sub "ARBrock" wrote: Wow Joel! Your response was very quick! I tried this code and what it did was move the summary task over one column -- which was helpful... but it didn't fill the name down the nonblank cells to the next cell in the column: This is what I started with: Area AreaName Task 1. SITE SETUP 1.1 Plans, permits, and special engineering 1.2 Site prep - removal of topsoil 1.3 Job shack and other rentals 2. DEMO 2.1 Remove exsisting hot tub 2.2 Demo patio and haul 3. POOL 3.1 Supply new pool When I run the code you gave me I end up with: Area AreaName Task 1. SITE SETUP 1.1 Plans, permits, and special engineering 1.2 Site prep - removal of topsoil 1.3 Job shack and other rentals 2. DEMO 2.1 Remove exsisting hot tub 2.2 Demo patio and haul 3. POOL 3.1 Supply new pool Now... If I can get something that will go through column B and notice that there is a cell that has something in it and then copy/paste or autofill it down to the next cell that has something in it... and then it will copy that cell and copy/paste or autofill it down.... This is what I want my end goal to look like: Area AreaName Task 1. SITE SETUP 1.1 SITE SETUP Plans, permits, and special engineering 1.2 SITE SETUP Site prep - removal of topsoil 1.3 SITE SETUP Job shack and other rentals 2. DEMO 2.1 DEMO Remove exsisting hot tub 2.2 DEMO Demo patio and haul 3. POOL 3.1 POOL Supply new pool If I could get the worksheet to here, I would be ever so grateful! -AR "Joel" wrote: Are the number in the first column numbers or text? thsi wil work on numbers If they are not numbers then modify as follows: from If Cells(RowCount, "A").Value = _ Int(Cells(RowCount, "A").Value) Then to If Val(Cells(RowCount, "A").Value) = _ Int(Val(Cells(RowCount, "A").Value)) Then Sub test() LastRow = Cells(Rows.Count, "A").End(xlUp).Row For RowCount = 2 To LastRow If Cells(RowCount, "A").Value = _ Int(Cells(RowCount, "A").Value) Then Cells(RowCount, "C").Cut _ Destination:=Cells(RowCount, "B") End If Next RowCount End Sub "ARBrock" wrote: Good morning, I am really new at programming in excel -- I've recorded a few macros, but now I am needing functionality that is going to require me to program something. I was hoping to find some help... I've got a worksheet in a workbook that contains column values that are numbered like this: 1. 1.1 1.2 1.3 1.4 (ect) The column to the right of this column is blank, and the column next to the blank column contains task type information. For the tasks numbered 1. (with no subnumber), the data in the task column is essentially a heading (if you are thinking in terms of project management, that information is a summary task). I need to have a function that will run through my column (it is column b) and any time it hits a summary number 1. , 2. , 3. , 4. (ect) I need it to look to the right 2 columns and pull that summary data and do 2 things with it: 1. Copy and paste it onto another worksheet (I need the number, the blank column, and the summary task -- so three columns). 2. Copy the summary task data and put it in the blank column for the non summary tasks. It kind of looks like this right now: Area AreaName Task 1. SITE SETUP 1.1 Plans, permits, and special engineering 1.2 Site prep - removal of topsoil 1.3 Job shack and other rentals 2. DEMO 2.1 Remove exsisting hot tub 2.2 Demo patio and haul 3. POOL 3.1 Supply new pool This is what I want it to look like: Area AreaName Task 1. SITE SETUP 1.1 SITE SETUP Plans, permits, and special engineering 1.2 SITE SETUP Site prep - removal of topsoil 1.3 SITE SETUP Job shack and other rentals 2. DEMO 2.1 DEMO Remove exsisting hot tub 2.2 DEMO Demo patio and haul 3. POOL 3.1 POOL Supply new pool Does that make sense? Thanks in advance - this will make my life SOO much easier! -AshleyRose |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Then and Copy/Paste
I forgot i piece of the change. Sorry Sub test() AreaName = "" LastRow = Cells(Rows.Count, "A").End(xlUp).Row For RowCount = 2 To LastRow If Cells(RowCount, "A").Value = _ Int(Cells(RowCount, "A").Value) Then AreaName = Cells(RowCount, "C").Value Cells(RowCount, "B").Value = AreaName Cells(RowCount, "C").ClearContents Else Cells(RowCount, "B").Value = AreaName End If Next RowCount End Sub "ARBrock" wrote: Wow Joel! Your response was very quick! I tried this code and what it did was move the summary task over one column -- which was helpful... but it didn't fill the name down the nonblank cells to the next cell in the column: This is what I started with: Area AreaName Task 1. SITE SETUP 1.1 Plans, permits, and special engineering 1.2 Site prep - removal of topsoil 1.3 Job shack and other rentals 2. DEMO 2.1 Remove exsisting hot tub 2.2 Demo patio and haul 3. POOL 3.1 Supply new pool When I run the code you gave me I end up with: Area AreaName Task 1. SITE SETUP 1.1 Plans, permits, and special engineering 1.2 Site prep - removal of topsoil 1.3 Job shack and other rentals 2. DEMO 2.1 Remove exsisting hot tub 2.2 Demo patio and haul 3. POOL 3.1 Supply new pool Now... If I can get something that will go through column B and notice that there is a cell that has something in it and then copy/paste or autofill it down to the next cell that has something in it... and then it will copy that cell and copy/paste or autofill it down.... This is what I want my end goal to look like: Area AreaName Task 1. SITE SETUP 1.1 SITE SETUP Plans, permits, and special engineering 1.2 SITE SETUP Site prep - removal of topsoil 1.3 SITE SETUP Job shack and other rentals 2. DEMO 2.1 DEMO Remove exsisting hot tub 2.2 DEMO Demo patio and haul 3. POOL 3.1 POOL Supply new pool If I could get the worksheet to here, I would be ever so grateful! -AR "Joel" wrote: Are the number in the first column numbers or text? thsi wil work on numbers If they are not numbers then modify as follows: from If Cells(RowCount, "A").Value = _ Int(Cells(RowCount, "A").Value) Then to If Val(Cells(RowCount, "A").Value) = _ Int(Val(Cells(RowCount, "A").Value)) Then Sub test() LastRow = Cells(Rows.Count, "A").End(xlUp).Row For RowCount = 2 To LastRow If Cells(RowCount, "A").Value = _ Int(Cells(RowCount, "A").Value) Then Cells(RowCount, "C").Cut _ Destination:=Cells(RowCount, "B") End If Next RowCount End Sub "ARBrock" wrote: Good morning, I am really new at programming in excel -- I've recorded a few macros, but now I am needing functionality that is going to require me to program something. I was hoping to find some help... I've got a worksheet in a workbook that contains column values that are numbered like this: 1. 1.1 1.2 1.3 1.4 (ect) The column to the right of this column is blank, and the column next to the blank column contains task type information. For the tasks numbered 1. (with no subnumber), the data in the task column is essentially a heading (if you are thinking in terms of project management, that information is a summary task). I need to have a function that will run through my column (it is column b) and any time it hits a summary number 1. , 2. , 3. , 4. (ect) I need it to look to the right 2 columns and pull that summary data and do 2 things with it: 1. Copy and paste it onto another worksheet (I need the number, the blank column, and the summary task -- so three columns). 2. Copy the summary task data and put it in the blank column for the non summary tasks. It kind of looks like this right now: Area AreaName Task 1. SITE SETUP 1.1 Plans, permits, and special engineering 1.2 Site prep - removal of topsoil 1.3 Job shack and other rentals 2. DEMO 2.1 Remove exsisting hot tub 2.2 Demo patio and haul 3. POOL 3.1 Supply new pool This is what I want it to look like: Area AreaName Task 1. SITE SETUP 1.1 SITE SETUP Plans, permits, and special engineering 1.2 SITE SETUP Site prep - removal of topsoil 1.3 SITE SETUP Job shack and other rentals 2. DEMO 2.1 DEMO Remove exsisting hot tub 2.2 DEMO Demo patio and haul 3. POOL 3.1 POOL Supply new pool Does that make sense? Thanks in advance - this will make my life SOO much easier! -AshleyRose |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
help w/ generic copy & paste/paste special routine | Excel Programming | |||
Excel cut/Paste Problem: Year changes after data is copy and paste | Excel Discussion (Misc queries) | |||
I cannot paste from one workbook to another. Copy works, paste do. | Excel Discussion (Misc queries) | |||
Copy and Paste macro needs to paste to a changing cell reference | Excel Programming |