if not empty do something

H

Helmut

I have order form based on info from catalogue.
If item not in catalogue, people will enter into bottom lines in column C.
IF one, two or more items were added, THEN I need to copy last cells from
column A and B and last cells from column G and H DOWN to as many rows as
there were new items added.
Hope this is clear. If someone could give me the code for this.
Thanks
Helmut
 
S

Simon Lloyd

Helmut;343628 said:
I have order form based on info from catalogue.
If item not in catalogue, people will enter into bottom lines in column
C.
IF one, two or more items were added, THEN I need to copy last cells
from
column A and B and last cells from column G and H DOWN to as many rows
as
there were new items added.
Hope this is clear. If someone could give me the code for this.
Thanks
HelmutYou say if one, two or more items was added.....then really you mean if
Click to expand...
anything was added?, what is the "Bottom" line in column C?, do you mean
you want to copy all cells from columns A:B below the last cell in
column C to the end of A:B? the same with G:H?, what do you want to do
with them after you have copied them?


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
 
H

Helmut

Simon,
a) Yes, if anything is added...i.e. if more than one row added, then need
"loop"
b) No, let me try to explain:

H G F E D C B
A
XXXXXX XXXXXX XXXXX XXXXX XXXXX XXXXX XXXXX XXXXX 15
NEW ITEM
16
NEW ITEM
17

IF NEW ITEM i.e. "B:15" = Empty, but "E16"=anything, THEN
copy "G&H:15" to "G&H:16" and "A&B15" to "A&B:16"

H G F E D C B
A
XXXXXX XXXXXX XXXXX XXXXX XXXXX XXXXX XXXXX XXXXX 15
XXXXXX XXXXXX NEW ITEM XXXXX XXXXX 16
NEW ITEM
17

Loop:

IF NEW ITEM i.e. "B:16" = Empty, but "E17"=anything, THEN
copy "G&H:16" to "G&H:17" and "A&B16" to "A&B:17"

UNTIL no more new items

Hope this is clear....thanks
 
S

Simon Lloyd

Helmut, the only problem i can see with your explanation is that if you
move A&B15 to A&B16 then they themselves become new items, your loop may
become endless, i see you have joined our forum, why not attach a
workbook in this thread showing your BEFORE on one sheet and desired
results on another (note the link to this thread below, if you post in
the same thread those that have been following or helping can still do
so)

Attatchments.

To upload a workbook, click reply then add your few words, scroll down
past the submit button and you will see the Manage Attatchments button,
this is where you get to add files for upload, if you have any trouble
please use this link or the one at the bottom of the
any page.



Helmut;345125 said:
Simon,
a) Yes, if anything is added...i.e. if more than one row added, then
need
"loop"
b) No, let me try to explain:

H G F E D C B
A
XXXXXX XXXXXX XXXXX XXXXX XXXXX XXXXX XXXXX XXXXX 15
NEW ITEM
16
NEW ITEM
17

IF NEW ITEM i.e. "B:15" = Empty, but "E16"=anything, THEN
copy "G&H:15" to "G&H:16" and "A&B15" to "A&B:16"

H G F E D C B
A
XXXXXX XXXXXX XXXXX XXXXX XXXXX XXXXX XXXXX XXXXX 15
XXXXXX XXXXXX NEW ITEM XXXXX XXXXX 16
NEW ITEM
17

Loop:

IF NEW ITEM i.e. "B:16" = Empty, but "E17"=anything, THEN
copy "G&H:16" to "G&H:17" and "A&B16" to "A&B:17"

UNTIL no more new items

Hope this is clear....thanks


Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
Click to expand...


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
 
H

Helmut Loeffler

Simon,
Could you please help me. I attached the latest file and when running
the code, I get an error:

If Cells(cell.Row + 1, "D") = "" Then
Range(Cells(cell.Row, "A"), Cells(cell.Row, "B")).Copy
Range(Cells(cell.Row + 1, "A"), Cells(cell.Row + 1,
"B")).PasteSpecial =
xlPasteAll
=====
here I get "Error 424 - Object Missing
=====
Range(Cells(cell.Row, "G"), Cells(cell.Row, "H")).Copy
Range(Cells(cell.Row + 1, "G"), Cells(cell.Row + 1,
"H")).PasteSpecial =
xlPasteAll
End If


+-------------------------------------------------------------------+
|Filename: CATALOGUE.xlsm |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=147|
+-------------------------------------------------------------------+
 
D

Don Guillett

Without looking at your file, try this idea

Sub copyifblank()
lr = Cells(Rows.Count, "b").End(xlUp).Row
For i = 2 To lr
If Cells(i, "d") = "" Then
Cells(i, "a").Resize(, 2).Copy Cells(i + 1, "a")
Cells(i, "g").Resize(, 2).Copy Cells(i + 1, "g")
End If
Next i
End Sub
 
H

Helmut Loeffler

Don said:
Without looking at your file, try this idea

Sub copyifblank()
lr = Cells(Rows.Count, "b").End(xlUp).Row
For i = 2 To lr
If Cells(i, "d") = "" Then
Cells(i, "a").Resize(, 2).Copy Cells(i + 1, "a")
Cells(i, "g").Resize(, 2).Copy Cells(i + 1, "g")
End If
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
message
Click to expand...

Thanks for trying to help. No it didn't work either. I changed logic of
procedure and now don't need this particular aspect. Thanks for your
help.
Thread closed.
Helmut
 
Top