Have a column with names in A and numbers in Columns B and C
I want the activecell to start in A4. IF B4=B5 and C4=C5 Then I
want the activecell in A4 to copy itself, Insert Shift:=xlDown,
and paste in cell A4,which is empty now because the cell contents
were shifted down.Then I want the active cell in A4 offset(1).
Else Just offset(1) when the if statement is false.

Then I want the process repeated till the last name in column A is use
in a relative
way.In other words the next sequence would start with activecell in A
due to the
offset and if B5 = B6 and C5=C6 Then etc.I've only had partial succes
writing this.


Thank

--
ligh
-----------------------------------------------------------------------
light's Profile: http://www.excelforum.com/member.php...nfo&userid=722
View this thread: http://www.excelforum.com/showthread.php?threadid=39963

Hopefully you will get an answer, but since you didn't get one when you
posted this earlier it probably means

a) it isn't easily done OR
b) no one knows what you are asking

So just posting it over and over may not be the best way to get a response.

--
Regards,
Tom Ogilvy

"light" wrote in
message ...

Have a column with names in A and numbers in Columns B and C
I want the activecell to start in A4. IF B4=B5 and C4=C5 Then I
want the activecell in A4 to copy itself, Insert Shift:=xlDown,
and paste in cell A4,which is empty now because the cell contents
were shifted down.Then I want the active cell in A4 offset(1).
Else Just offset(1) when the if statement is false.

Then I want the process repeated till the last name in column A is used
in a relative
way.In other words the next sequence would start with activecell in A5
due to the
offset and if B5 = B6 and C5=C6 Then etc.I've only had partial success
writing this.


Thanks


--
light
------------------------------------------------------------------------
light's Profile:
http://www.excelforum.com/member.php...fo&userid=7228
View this thread: http://www.excelforum.com/showthread...hreadid=399631

I'm a total novice.I thought this was too easy and that's why I didn'
get a response.
In case it's not clear the list of names in column A is much shorte
than B and C. The names in Column A need to be duplicated to match th
correct data in B and C so that when you read accross the row startin
at A it has the correct data in the rows of B and C directly acros
from it..Right now all the names in column A are offset to the correc
data in B and C

--
ligh
-----------------------------------------------------------------------
light's Profile: http://www.excelforum.com/member.php...nfo&userid=722
View this thread: http://www.excelforum.com/showthread.php?threadid=39963

is this what you want?

Sub CopyIfTrue()
Dim ColA As Range
Dim ColB As Range
Dim ColC As Range

Set ColA = Range("A:A")
Set ColB = Range("B:B")
Set ColC = Range("C:C")

Dim RowIndex As Long
Dim NumRows As Long

NumRows = ColA.Rows.Count

For RowIndex = 4 To NumRows - 1 'start at row 4
If ColB.Item(RowIndex) = ColB.Item(RowIndex + 1) And _
ColC.Item(RowIndex) = ColC.Item(RowIndex + 1) Then
ColA.Item(RowIndex + 1).Value = ColA.Item(RowIndex).Value
End If
Next RowIndex
End Sub


your explanation is a bit too vague...

light Wrote:
Have a column with names in A and numbers in Columns B and C
I want the activecell to start in A4. IF B4=B5 and C4=C5 Then I
want the activecell in A4 to copy itself, Insert Shift:=xlDown,
and paste in cell A4,which is empty now because the cell contents
were shifted down.Then I want the active cell in A4 offset(1).
Else Just offset(1) when the if statement is false.

Then I want the process repeated till the last name in column A is use
in a relative
way.In other words the next sequence would start with activecell in A
due to the
offset and if B5 = B6 and C5=C6 Then etc.I've only had partial succes
writing this.


Thank

--
T-®e
-----------------------------------------------------------------------
T-®ex's Profile: http://www.excelforum.com/member.php...fo&userid=2657
View this thread: http://www.excelforum.com/showthread.php?threadid=39963

T Rex

Thanks for trying. When I run the macro,it makes the cursor shakes
for the 10 seconds it runs and then only assigns 10 out of the 100
names .Deletes the other names in Column A.

Try to give another example.Names in Column A appear only once.
Column A4=Banana A5=Apple A6=Strawberry.

Columns B and C is the data for items in Column A.That data appears
multiple times. So data for Banana is in B4:B10 and C4:C10. Apple data
is in B11:B16 and C11:C16. Strawberry data is in B17:B24 and C17:C24.


So I need Banana to appear in A4:A10,Apple to appear in A11:A16
and Strawberry in A17:A24.


--
light
------------------------------------------------------------------------
light's Profile: http://www.excelforum.com/member.php...fo&userid=7228
View this thread: http://www.excelforum.com/showthread...hreadid=399631

This might be what you want.

Sub AA()
Dim rng As Range
Dim rng1 As Range
Set rng = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp)).Offset(0, -1)
On Error Resume Next
Set rng1 = rng.SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng1 Is Nothing Then
rng1.Formula = "=" & rng1(1).Offset(-1, 0).Address(0, 0)
rng.Formula = rng.Value
End If
End Sub


--
Regards,
Tom Ogilvy


"light" wrote in
message ...

T Rex

Thanks for trying. When I run the macro,it makes the cursor shakes
for the 10 seconds it runs and then only assigns 10 out of the 100
names .Deletes the other names in Column A.

Try to give another example.Names in Column A appear only once.
Column A4=Banana A5=Apple A6=Strawberry.

Columns B and C is the data for items in Column A.That data appears
multiple times. So data for Banana is in B4:B10 and C4:C10. Apple data
is in B11:B16 and C11:C16. Strawberry data is in B17:B24 and C17:C24.


So I need Banana to appear in A4:A10,Apple to appear in A11:A16
and Strawberry in A17:A24.


--
light
------------------------------------------------------------------------
light's Profile:
http://www.excelforum.com/member.php...fo&userid=7228
View this thread: http://www.excelforum.com/showthread...hreadid=399631

Thanks Tom,but all that did was copy the last name in column A to the
bottom of the sheet .Didn't touch the other names.


--
light
------------------------------------------------------------------------
light's Profile: http://www.excelforum.com/member.php...fo&userid=7228
View this thread: http://www.excelforum.com/showthread...hreadid=399631

I may be over-simplifying it, but this adaptation of Tom's code worked for
the sample you gave:

Sub tester()

Dim rng As Range
Dim c As Range

' assumes that data starts in row 4
' and that value in column B determines what should be in column A

Set rng = Range(Cells(4, 2), Cells(Rows.Count, 2).End(xlUp)).Offset(0, -1)

For Each c In rng
c.Formula = "=" & rng.Offset(0, 1).Address(0, 0) ' set column A equal to
column B
c.Formula = c.Value
Next c
End Sub

Regards,

Patti



"light" wrote in
message ...

Thanks Tom,but all that did was copy the last name in column A to the
bottom of the sheet .Didn't touch the other names.


--
light
------------------------------------------------------------------------
light's Profile:
http://www.excelforum.com/member.php...fo&userid=7228
View this thread: http://www.excelforum.com/showthread...hreadid=399631

I assumed a different layout (jumped to a conclusion), but going back an
rereading your description, I see all your names are nested at the top of
column A. See if this does it.

Sub EFGH()
Dim r As Range, v As Variant
Dim i As Long, j As Long
Set r = Range(Range("A4"), Range("A4").End(xlDown))
v = r.Value
r.Clear
i = 4
j = 0
Do While Not IsEmpty(Cells(i, 2))
If Cells(i, 2) < Cells(i - 1, 2) Or Cells(i, 3) < Cells(i - 1, 3) Then
j = j + 1
End If
Cells(i, 1).Value = v(j, 1)
i = i + 1
Loop

End Sub

--
Regards,
Tom Ogilvy


"light" wrote in
message ...

Thanks Tom,but all that did was copy the last name in column A to the
bottom of the sheet .Didn't touch the other names.


--
light
------------------------------------------------------------------------
light's Profile:
http://www.excelforum.com/member.php...fo&userid=7228
View this thread: http://www.excelforum.com/showthread...hreadid=399631

Tom

You da man. Works great. Thanks again. Thanks Patti too but I got
an error message with yours.


--
light
------------------------------------------------------------------------
light's Profile: http://www.excelforum.com/member.php...fo&userid=7228
View this thread: http://www.excelforum.com/showthread...hreadid=399631