Syntax Sucks!

[Bennyton]

Hey all. can anyone tell me whats wrong with this?

Sub StopFind()
'
'
'
Sheets("combine450_60 level").Select
Columns("A:D").Select
Selection.ClearContents
Windows("60 STOP macro.xls").Activate
Range("A:A,G:G").Select
Range("G1").Activate
Selection.Copy
Windows("MATT NOT SAP MACRO1.xls").Activate
Range("A1").Select
ActiveSheet.Paste
Windows("450 STOP macro.XLS").Activate
Range("A:A,G:G").Select
Range("G1").Activate
Application.CutCopyMode = False
Selection.Copy
Windows("MATT NOT SAP MACRO1.xls").Activate
Range("C1").Select
ActiveSheet.Paste
'
'Test to search for stop statuses in "combine450_60 level"
'when it finds a stop status, it compares the serial number next to it, to those in column A in the WIP work sheet
'if it finds a match, it will copy and paste that column into the "stop sap or matt" sheet,
'and delete the row from the WIP sheet
SerialCol = "A"
SerialCol1 = "C"
crit1 = "STOP"

Dim CellR As Range
Dim strName As String
Dim Arr() As String

lastRow1 = Sheets("stop sap or matt").Range("A1").End(xlDown).Row


lastRow2 = Sheets("WIP").Range("A1").End(xlDown).Row

lastRow3 = Sheets("combine450_60 level").Range("A1").End(xlDown).Row
For i = 1 To lastRow3

lastRow4 = Sheets("combine450_60 level").Range("C1").End(xlDown).Row
For h = 1 To lastRow4

If Sheets("combine450_60 level").Cells(i, "B").Value = crit1 Then
For j = 1 To lastRow2
If Sheets("WIP").Cells(j, SerialCol).Value = Sheets("combine450_60 level").Cells(i, SerialCol).Value Then
Sheets("WIP").Rows(j).EntireRow.Cut Sheets("stop sap or matt").Range(lastRow1 + 1 & ":" & lastRow1 + 1)
Sheets("WIP").Rows(j).EntireRow.Delete

End If

Next j

ElseIf Sheets("combine450_60 level").Cells(h, "D").Value = crit1 Then
For j = 1 To lastRow2
If Sheets("WIP").Cells(j, SerialCol).Value = Sheets("combine450_60 level").Cells(h, SerialCol1).Value Then
Sheets("WIP").Rows(j).EntireRow.Cut Sheets("stop sap or matt").Rows(lastRow1 + 1 & ":" & lastRow1 + 1)
Sheets("WIP").Rows(j).EntireRow.Delete
End If
Next j
Next h
Next i
End If
End Sub

 

[Andrew Poulsom]

You are missing an End If between Next j and Next h. It would be easier to find if you indented your code and used CODE tags when posting to the Board:

http://www.mrexcel.com/forum/misc.php?do=bbcode#code

 

[Bennyton]

You are missing an End If between Next j and Next h. It would be easier to find if you indented your code and used CODE tags when posting to the Board:

http://www.mrexcel.com/forum/misc.php?do=bbcode#code

Thanks for the help Andrew!

Sorry about the indention, it was indented in notepad+ but came out flat...

and I forgot about the quote tags... my bad

 

[Bennyton]

Actually Andrew, I just tried that, and it seemed to run, but caused excel to crash....


the part I'm most worried about is the ElseIf part.

could you have another look please?

Sub StopFind()
'
'
'
Sheets("combine450_60 level").Select
Columns("A:D").Select
Selection.ClearContents
Windows("60 STOP macro.xls").Activate
Range("A:A,G:G").Select
Range("G1").Activate
Selection.Copy
Windows("MATT NOT SAP MACRO1.xls").Activate
Range("A1").Select
ActiveSheet.Paste
Windows("450 STOP macro.XLS").Activate
Range("A:A,G:G").Select
Range("G1").Activate
Application.CutCopyMode = False
Selection.Copy
Windows("MATT NOT SAP MACRO1.xls").Activate
Range("C1").Select
ActiveSheet.Paste
'
'Test to search for stop statuses in "combine450_60 level"
'when it finds a stop status, it compares the serial number next to it, to those in column A in the WIP work sheet
'if it finds a match, it will copy and paste that column into the "stop sap or matt" sheet,
'and delete the row from the WIP sheet
SerialCol = "A"
SerialCol1 = "C"
crit1 = "STOP"

Dim CellR As Range
Dim strName As String
Dim Arr() As String
            
            lastRow1 = Sheets("stop sap or matt").Range("A1").End(xlDown).Row
            
            
            lastRow2 = Sheets("WIP").Range("A1").End(xlDown).Row
            
            lastRow3 = Sheets("combine450_60 level").Range("A1").End(xlDown).Row
            For i = 1 To lastRow3
            
            lastRow4 = Sheets("combine450_60 level").Range("C1").End(xlDown).Row
            For h = 1 To lastRow4

                     If Sheets("combine450_60 level").Cells(i, "B").Value = crit1 Then
             For j = 1 To lastRow2
                        If Sheets("WIP").Cells(j, SerialCol).Value = Sheets("combine450_60 level").Cells(i, SerialCol).Value Then
                       Sheets("WIP").Rows(j).EntireRow.Cut Sheets("stop sap or matt").Range(lastRow1 + 1 & ":" & lastRow1 + 1)
                       Sheets("WIP").Rows(j).EntireRow.Delete

        End If
  Next j

                       ElseIf Sheets("combine450_60 level").Cells(h, "D").Value = crit1 Then
                     For j = 1 To lastRow2
                If Sheets("WIP").Cells(j, SerialCol).Value = Sheets("combine450_60 level").Cells(h, SerialCol1).Value Then
                Sheets("WIP").Rows(j).EntireRow.Cut Sheets("stop sap or matt").Rows(lastRow1 + 1 & ":" & lastRow1 + 1)
            Sheets("WIP").Rows(j).EntireRow.Delete
        End If
  Next j
End If
  Next h
Next i
                
End If
            
End Sub

thanks

 

[Bennyton]

sorry, had trouble indenting it, its indented now.

 

[Andrew Poulsom]

Crash in what way?

 

[Bennyton]

I'm not sure why, but it crashed the whole lot of excel, rather than just the macro part.



I was wondering, would it be possible to take the information from column's C&D and paste them onto the end of A&B, and change the code to just perform the search with those two columns?

 

[Andrew Poulsom]

Can you explain in words what you are trying to do. A sample of your data might help.

 

[Bennyton]

Yup.

Well, there are 3 worksheets,

the first one is called " Stop SAP & Matt"

the second is called "WIP"

the third is called "Combine450_60 level"

What I'm trying to do, is import 4 columns of data into the third worksheet...

then I want to check column A's values (serial code's) against those in column A in WIP, as long as colimn B says "STOP"

But I also need to check column C's values (serial code's) against those in column A in WIP, as long as colimn D says "STOP"

if it finds a match, it takes that row out of WIP, and pastes in the last row of Stop SAP & MATT

It then deletes the same row from WIP.

It actually worked yesterday for column A and C...

but I can't get it working now...since I introduced H....

if that makes any sense...


Thanks a million for your time btw

 

[Andrew Poulsom]

Which sheets are in which workbook? You seem to have 3.