If Then and Copy/Paste

[ARBrock]

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

 

[Joel]

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]

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]

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

 

[Joel]

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