VBA Error, But Can't Fix It

SonicBoomGolf

Active Member
Joined
Aug 7, 2004
Messages
325
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?
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,097
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Aug 7, 2004
Messages
325
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
ABCD
1MixingCenter PickupDateAppt#Order#Delivery#
212/26/2004
3#################00003637220080487514
4727736223,145
501/21/2005
600003718530080702169
700003718540080702170
800005045520080694520
9#################00004133870080673555
10#################00004133890080673557
11#################00004709700080694076
12#################00003432100080692690
136,967761,772######
Practice


I don't understand why this is not working.

This is the end result I am looking for:
FTW1.xls
ABCD
1MixingCenter PickupDateAppt#Order#Delivery#
212/26/2004######00003637220080487514
301/21/200500003718530080702169
401/21/200500003718540080702170
501/21/200500005045520080694520
601/21/2005######00004133870080673555
701/21/2005######00004133890080673557
801/21/2005######00004709700080694076
901/21/2005######00003432100080692690
Practice
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,097
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Aug 7, 2004
Messages
325
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,097
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:
Code:
        Cells(toprw - 1, 2).Cut
        Range(Cells(toprw, 2), Cells(botrw - 3, 2)).Paste
to
Code:
        Cells(toprw - 1, 2).Copy
        Range(Cells(toprw, 2), Cells(botrw - 3, 2)).Select
        ActiveSheet.Paste
        Cells(toprw - 1, 2).ClearContents
 

SonicBoomGolf

Active Member
Joined
Aug 7, 2004
Messages
325
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
ABCDEFGHIJKLMNOPQ
1MixingCenter PickupDateAppt#Order#Delivery#Order StatusOrderTypeItemTypesMaster Shipment#SCACPurchaseOrder#CustomerNameCitySt.Total CasesEst.PalletsLoose CasesWeight
212/26/2004
312/26/041:00:PM10907800003637220080487514LOADCUSTGROC00001683186MHAEI1-631230NBINSTITUTIONALJOBBERSINLOUISVILLETN727736223,145
4727736223,145616
501/21/2005
600003718530080702169PENDCUSTGROC00001789870MHAEI279468BRUNOSSUPERMARKETSINCGROCERYBIRMINGHAMAL1,6721486437,854
700003718540080702170PENDCUSTGROC00001789870MHAEI279469BRUNOSSUPERMARKETSINCGROCERYBIRMINGHAMAL96304,776
800005045520080694520STAGCUSTGROC,SNCKCP0000504552CPU335856HARRISTEETERSUPERMARKETGREENSBORONC2,2982462740,462
901/21/051:00:AM11292600004133870080673555SHRTCUSTSNCK00001799754MSCNN879127ECKERDCORPORATIONCHARLOTTENC39661085,883
1001/21/051:00:AM11292600004133890080673557SHRTCUSTSNCK00001799754MSCNN972312ECKERDCORPORATIONCHARLOTTENC2523362,451
1101/21/051:00:AM11292600004709700080694076SHRTCUSTSNCK00001799754MSCNN385190ECKERDCORPORATIONCHARLOTTENC4295694,406
1201/21/057:00:PM11314000003432100080692690PRLSCUSTGROCCP0000343210CPU859386FREDSDCMEMPHISMEMPHISTN1,824216835,984
136,967761,772131,8164,379
Practice
 

SonicBoomGolf

Active Member
Joined
Aug 7, 2004
Messages
325
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

Active Member
Joined
Aug 7, 2004
Messages
325
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
ABCDEFGHI
1LocationMixingCenter PickupDateAppt#Order#Delivery#Order StatusOrderTypeItemTypesMaster Shipment#
2
312/26/2004######00003637220080487514LOADCUSTGROC00001683186M
4727736223,145616
5
601/05/2005######00002716320080617581LOADCUSTGROC00001724202M
722021016,892176
8
901/06/2005######00004599310080633382PENDCUSTGROC00001729285M
1076494816,599422
11
1201/08/2005######00004242650080620810LOADCUSTGROC00001750997M
131,1941634641,011935
14
1501/11/2005######00004117470080541797STAGCUSTGROCCP0000411747
161,90821033,155959
17
1801/15/2005######00002848640080567279LOADCUSTGROC00001730246M
191,047466918,279475
20
2101/18/200500004957680080691482STAGCUSTGROC
2201/18/200500005031550080691497STAGCUSTSNCK
231,34041,03525,658697
Practice
 

Forum statistics

Threads
1,147,846
Messages
5,743,520
Members
423,801
Latest member
paulj4177

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top