| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
if not empty do something
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if not empty do something
Helmut;343628 Wrote: 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 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) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=96127 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if not empty do something
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 "Simon Lloyd" wrote: Helmut;343628 Wrote: 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 You say if one, two or more items was added.....then really you mean if 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) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=96127 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if not empty do something
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 Wrote: 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 "Simon Lloyd" wrote: Helmut;343628 Wrote: 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 You say if one, two or more items was added.....then really you mean if 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' (http://www.thecodecage.com/)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'if not empty do something - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=96127) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=96127 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if not empty do something
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| +-------------------------------------------------------------------+ -- Helmut Loeffler ------------------------------------------------------------------------ Helmut Loeffler's Profile: http://www.thecodecage.com/forumz/member.php?userid=311 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=96127 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if not empty do something
Don Guillett;359288 Wrote: 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 "Helmut Loeffler" wrote in message ... 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| +-------------------------------------------------------------------+ -- Helmut Loeffler ------------------------------------------------------------------------ Helmut Loeffler's Profile: 'The Code Cage Forums - View Profile: Helmut Loeffler' (http://www.thecodecage.com/forumz/member.php?userid=311) View this thread: 'if not empty do something - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=96127) 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 -- Helmut Loeffler ------------------------------------------------------------------------ Helmut Loeffler's Profile: http://www.thecodecage.com/forumz/member.php?userid=311 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=96127 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| find empty cells in a column then append row that empty cell is in | Excel Programming | |||
| Delete Rows with Empty Cells with empty column 1 | Excel Programming | |||
| Finding next empty empty cell in a range of columns | Excel Programming | |||
| Excel - Autom. Filter "Empty / Non Empty cells" should come first | Excel Discussion (Misc queries) | |||
| Can blank cells created using empty Double-Quotes not be empty?? | Excel Programming | |||
Linear Mode
