Run time error '92': For loop not initialized - HELP!

erimhast

New Member
Joined
Aug 12, 2014
Messages
36
HEllo,
Please see the below code i have used to copy rows of data with specific text in a cell from one worksheet in excel to another. The code worked twice and now i am getting the run time error 92. Please check out my coding and let me know where i have gone wrong. I appreciate the help in advanace.

Dim objcell As Object

For Each objcell In ActiveSheet.Cells
Sheets("PBB Data").Select
Columns("AB:AB").Select
Selection.Find(What:="LD", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate
ActiveCell.Offset(0, -27).Select
ActiveCell.EntireRow.Select
Selection.Cut
Sheets("LD").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Next objcell
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Where's it falling apart?

Note that instead of looping it'll be easier to use Autofilter to show just the rows matching your criteria, then you can use Goto-->Special-->Visible Cells only and cut/paste.
 
Upvote 0
I could not use the visible cells only and cut/paste into another worksheet. Lucky for me all of the data i needed was grouped together so i could select everything but the header (which was visible) and cut and paste it into another worksheet.
 
Upvote 0
the option to cut and paste won't work since i am creating the automation on this report for monthly data that changes. Below is the coding that i have used and it works with only a slight problem. Now that i have the line to resume on error, once all of the data that i need to cut and paste to another worksheet is moved, the macro looks at the header and cuts that along with blank cells and completely overwrites all of my data pasted into another worksheet. How do i get the coding to stop after it finds the last cell in column AB that has LD in it?

Dim objcell As Object

For Each objcell In ActiveSheet.Cells
Sheets("PBB Data").Select
Columns("AB:AB").Select
Selection.Find(What:="LD", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate
ActiveCell.Offset(0, -27).Select
ActiveCell.EntireRow.Select
Selection.Cut
Sheets("LD").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
On Error Resume Next
Next objcell
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top