VBA Question

[Wigman86]

Hello All

I am lost as to why my Macro won't work. The Macro actually runs and then just stays processing forever without the final result. I keep having to quit excel as it keeps going and going.
Basically I want to copy and paste anything that matches the criteria below and then delete the rows I have copied and pasted. The first IF statement works fine but it is the second IF statement for some reason where the trouble lies. Any help would be greatly appreciated.


Private Sub CommandButton1_Click()


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


Dim s1 As Worksheet, s2 As Worksheet, s3 As Worksheet
Set s1 = Sheets("Sheet 1")
Set s2 = Sheets("Sheet 2")
Set s3 = Sheets("Sheet 3")

a = s1.Cells(Rows.Count, 1).End(xlUp).Row


For i = a To 11 Step -1
b = s2.Cells(Rows.Count, 1).End(xlUp).Row



If (s1.Cells(i, 9) = "DDP" Or s1.Cells(i, 9) = "DDP 1" Or s1.Cells(i, 9) = "DDP 2" Or s1.Cells(i, 9) = "DDP 3" Or s1.Cells(i, 9) = "DDP 4") And s1.Cells(i, 33).Value > 0 Then
s1.Rows(i).Copy s2.Cells(b + 1, 1)
s1.Cells(i, 9).EntireRow.Delete


End If


If s1.Cells(i, 4).Value = "D" Then

b = s3.Cells(Rows.Count, 1).End(xlUp).Row
s1.Rows(i).Copy s3.Cells(b + 1, 1)
s1.Cells(i, 4).EntireRow.Delete


End If



Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic


MsgBox "Done!"


End Sub

 

[igold]

I have not really looked at your code yet and you did not say what error (I assume) it is throwing but could it be a simple typo...

Set s1 = Sheets("[COLOR=#ff0000]Sheet 1[/COLOR]")
Set s2 = Sheets("[COLOR=#ff0000]Sheet 2[/COLOR]")
Set s3 = Sheets("[COLOR=#ff0000]Sheet 3[/COLOR]")

As opposed to:

Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
Set s3 = Sheets("Sheet3")

 

[Wigman86]

I have not really looked at your code yet and you did not say what error (I assume) it is throwing but could it be a simple typo...

Set s1 = Sheets("[COLOR=#ff0000]Sheet 1[/COLOR]")
Set s2 = Sheets("[COLOR=#ff0000]Sheet 2[/COLOR]")
Set s3 = Sheets("[COLOR=#ff0000]Sheet 3[/COLOR]")

As opposed to:

Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
Set s3 = Sheets("Sheet3")

Hello IGold

No the sheet name is ok. I don't get an error message. The macro just continues to run and run until i need to ctrl+alt+dlt and close the file. It basically takes forever and nothing ends up happening as a result.

Thanks

 

[igold]

Yeah sorry about the "Error" part of my post, but I was being distracted and "blew" by that part.

Sorry about the confusion.

 

[Norie]

Have you tried stepping through the code with F8 to see what's happening?