VBA Error, But Can't Fix It

[SonicBoomGolf]

I am having a problem with the following code.

Public Sub OrderSummary()

botrw = 55
' Inserts a New Column in Column A
With Range("a:a").Select
Selection.Insert shift:=xlToRight
End With
Do
toprw = Range("m" & botrw).End(xlDown).Row
botrw = Range("m" & toprw).End(xlDown).Row
Cells(toprw - 1, 2).Cut
Range(Cells(toprow, 2), Cells(botrw - 2, 2)).Paste
Loop Until botrw = Cells(65536, 2).End(xlUp).Row
End Sub

I get a Run-Time 1004 Error when it comes to this portion of the code:
Range(Cells(toprow, 2), Cells(botrw - 2, 2)).Paste

I don't understand what is wrong with this. Any ideas?

 

[Joe4]

If you hit "Debug" when the error occurs, you can view your code in break code. If you place your cursor over the "toprw" and "botrw" variables in the line causing the error, it should tell you what their values are. If "botrw" is 2 or less, you will have a problem as you are trying to subtract 2 from it in a row calculation.

 

[SonicBoomGolf]

I never knew you could do that. I'll try it out. Thanks.

 

[SonicBoomGolf]

Does it matter if toprw and botrw are the same sometimes? I am attching a sample data set I am using. I cannot figure out why the Range(Cells(toprw, 2), Cells(botrw - 3, 2)).Paste part of the code is not working. Here is the full code.

Public Sub OrderSummary()

botrw = 1
' Inserts a New Column in Column A
With Range("a:a").Select
Selection.Insert shift:=xlToRight
End With
Do
toprw = Range("m" & botrw).End(xlDown).Row
botrw = Range("m" & toprw).End(xlDown).Row
Cells(toprw - 1, 2).Cut
Range(Cells(toprw, 2), Cells(botrw - 3, 2)).Paste
Loop Until botrw = Cells(65536, 2).End(xlUp).Row
End Sub

I step through the code, and when I look at the values assigned to the cells that I want to paste to, it all checks out. In my example below, I want to cut 12/26/04 (Cell B2) and paste it in the cell that has 12/26/04 1:00PM (Cell B3). When I step through the code, I look at the values assigned to Cells(toprw, 2) and Cells(botrw - 3, 2). Both equal 12/26/04 1:00, which is exactly where I want to paste the date I copied from Cell B2 (12/26/04).

FTW1.xls
	A	B	C	D
1	MixingCenter PickupDate	Appt#	Order#	Delivery#
2	12/26/2004
3	###########	######	0000363722	0080487514
4	727	7	362	23,145
5	01/21/2005
6			0000371853	0080702169
7			0000371854	0080702170
8			0000504552	0080694520
9	###########	######	0000413387	0080673555
10	###########	######	0000413389	0080673557
11	###########	######	0000470970	0080694076
12	###########	######	0000343210	0080692690
13	6,967	76	1,772	######
Practice

I don't understand why this is not working.

This is the end result I am looking for:

FTW1.xls
	A	B	C	D
1	MixingCenter PickupDate	Appt#	Order#	Delivery#
2	12/26/2004	######	0000363722	0080487514
3	01/21/2005		0000371853	0080702169
4	01/21/2005		0000371854	0080702170
5	01/21/2005		0000504552	0080694520
6	01/21/2005	######	0000413387	0080673555
7	01/21/2005	######	0000413389	0080673557
8	01/21/2005	######	0000470970	0080694076
9	01/21/2005	######	0000343210	0080692690
Practice

 

[Joe4]

It appears that one of the problem may be that the Cut area and Paste area are not the same size. It looks like you are cutting from one cell and pasting into four cells. Excel does not like that. It wants the Cut and Paste areas to be the same size.

 

[SonicBoomGolf]

Yes, my cut and paste areas will differ from time to time. Sometimes the paste range may be one cell and other times it may be over 100. Any way I can make Excel like it? Maybe if I copy, paste, then delete the cells I don't need?

In my sample above, the code does not even execute the portion that cuts from one cell and pastes in one cell. The cut (B2) and paste (B3) size are the same size, one cell, but it does not work.

 

[Joe4]

Unfortunately, I am unable to reproduce your example, because your macro is referencing column "M", and you did not post what is in column M.

Try changing:

        Cells(toprw - 1, 2).Cut
        Range(Cells(toprw, 2), Cells(botrw - 3, 2)).Paste

to

        Cells(toprw - 1, 2).Copy
        Range(Cells(toprw, 2), Cells(botrw - 3, 2)).Select
        ActiveSheet.Paste
        Cells(toprw - 1, 2).ClearContents

 

[SonicBoomGolf]

Sorry, I didn't include my full data set. I doesn't matter which column I use. Here is the full Data Set. I don't need rows 4 or 13 in this example, after the macro is run. I am going to try out what you suggested.

FTW1.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	MixingCenter PickupDate	Appt#	Order#	Delivery#	Order Status	OrderType	ItemTypes	Master Shipment#	SCAC	PurchaseOrder#	CustomerName	City	St.	Total Cases	Est.Pallets	Loose Cases	Weight
2	12/26/2004
3	12/26/041:00:PM	109078	0000363722	0080487514	LOAD	CUST	GROC	00001683186M	HAEI	1-631230NB	INSTITUTIONALJOBBERSIN	LOUISVILLE	TN	727	7	362	23,145
4	727	7	362	23,145	616
5	01/21/2005
6			0000371853	0080702169	PEND	CUST	GROC	00001789870M	HAEI	279468	BRUNOSSUPERMARKETSINCGROCERY	BIRMINGHAM	AL	1,672	14	864	37,854
7			0000371854	0080702170	PEND	CUST	GROC	00001789870M	HAEI	279469	BRUNOSSUPERMARKETSINCGROCERY	BIRMINGHAM	AL	96	3	0	4,776
8			0000504552	0080694520	STAG	CUST	GROC,SNCK	CP0000504552	CPU	335856	HARRISTEETERSUPERMARKET	GREENSBORO	NC	2,298	24	627	40,462
9	01/21/051:00:AM	112926	0000413387	0080673555	SHRT	CUST	SNCK	00001799754M	SCNN	879127	ECKERDCORPORATION	CHARLOTTE	NC	396	6	108	5,883
10	01/21/051:00:AM	112926	0000413389	0080673557	SHRT	CUST	SNCK	00001799754M	SCNN	972312	ECKERDCORPORATION	CHARLOTTE	NC	252	3	36	2,451
11	01/21/051:00:AM	112926	0000470970	0080694076	SHRT	CUST	SNCK	00001799754M	SCNN	385190	ECKERDCORPORATION	CHARLOTTE	NC	429	5	69	4,406
12	01/21/057:00:PM	113140	0000343210	0080692690	PRLS	CUST	GROC	CP0000343210	CPU	859386	FREDSDCMEMPHIS	MEMPHIS	TN	1,824	21	68	35,984
13	6,967	76	1,772	131,816	4,379
Practice

 

[SonicBoomGolf]

This worked for me...... I found out that there was some inconsistancy in the placement of the data, which was causing some errors. Thanks for the push over the edge. I would still be running in circles!!!!

Public Sub OrderSummary()

botrw = 1
' Inserts a New Column in Column A
With Range("a:a").Select
Selection.Insert shift:=xlToRight
End With
Do
toprw = Range("d" & botrw).End(xlDown).Row
botrw = Range("d" & toprw).End(xlDown).Row
Cells(toprw - 1, 2).Copy
Range(Cells(toprw, 2), Cells(botrw - 1, 2)).Select
ActiveSheet.Paste
Cells(toprw - 1, 2).ClearContents
Loop Until botrw = Cells(65536, 7).End(xlUp).Row
End Sub

 

[SonicBoomGolf]

How could I write code that loops to delete all the rows in Column B that do not have a date associated with them, as shown below?

FTW1.xls
	A	B	C	D	E	F	G	H	I
1	Location	MixingCenter PickupDate	Appt#	Order#	Delivery#	Order Status	OrderType	ItemTypes	Master Shipment#
2
3		12/26/2004	######	0000363722	0080487514	LOAD	CUST	GROC	00001683186M
4		727	7	362	23,145	616
5
6		01/05/2005	######	0000271632	0080617581	LOAD	CUST	GROC	00001724202M
7		220	2	101	6,892	176
8
9		01/06/2005	######	0000459931	0080633382	PEND	CUST	GROC	00001729285M
10		764	9	48	16,599	422
11
12		01/08/2005	######	0000424265	0080620810	LOAD	CUST	GROC	00001750997M
13		1,194	16	346	41,011	935
14
15		01/11/2005	######	0000411747	0080541797	STAG	CUST	GROC	CP0000411747
16		1,908	21	0	33,155	959
17
18		01/15/2005	######	0000284864	0080567279	LOAD	CUST	GROC	00001730246M
19		1,047	4	669	18,279	475
20
21		01/18/2005		0000495768	0080691482	STAG	CUST	GROC
22		01/18/2005		0000503155	0080691497	STAG	CUST	SNCK
23		1,340	4	1,035	25,658	697
Practice