Error pop up: compile error sub or function not definedwhen executing macro

[hrh_dash]

Pop out error: Run time error 13 Type Mismatch when executing macro.

Error was displayed at this line of code below:
Set successrng = Find(What:="Success", After:=ActiveCell, LookIn:=xlValues _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)

Below is the code so far:

Sub daily_instalment()
    
    Dim ws          As Worksheet
    Dim lastRow     As Long
    Dim i           As Long
    Dim successrng   As Long
    
    For Each ws In Worksheets
        
        ws.Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
                                        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                                        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
                                        :=";"
        
        ws.Range("H:H").EntireColumn.Insert
        
        lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).EntireRow.Row
        
        With ws.Cells
            
            Set successrng = ws.Cells.Find(What:="Success", After:=ActiveCell, LookIn:=xlValues _
                , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False)
            
             ws.Rows(successrng).EntireRow.Delete
        
            
        End With
        
        For i = 3 To lastRow
            
            Cells(i, 8).Value = Cells(i, 6).Value * Cells(i, 7).Value
            
        Next i
        
    Next
    
End Sub

thanks and appreciate the help in advance..

[#2]

@hrh_dash 

You declare successrng as a Long but then set it to a Range. Change

            Set successrng = ws.Cells.Find(What:="Success", After:=ActiveCell, LookIn:=xlValues _
                , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False)

to

            successrng = ws.Cells.Find(What:="Success", After:=ActiveCell, LookIn:=xlValues _
                , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False).Row

Apart from that, you don't need the lines With ws.Cells and End With. You don't use them for anything.

[#3]

@Hans Vogelaar , thanks for the advice. 

I would like to use a For loop to delete the entire row if "Success" is found in each row. However, i got an error msg pop up stating "Run time error 424, Object required".

Below is the code:

Sub daily_instalment()
    
    Dim ws          As Worksheet
    Dim lastRow     As Long
    Dim i           As Long
    Dim successrng   As Long
    Dim cell        As Range
    Dim successrange As Range
    
    For Each ws In Worksheets
        
        ws.Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
                                        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                                        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
                                        :=";"
        
        ws.Rows("1").EntireRow.Delete
        
        ws.Range("A1").CurrentRegion.EntireColumn.AutoFit
        
        lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).EntireRow.Row
        
        ws.Rows(lastRow).EntireRow.Delete
        
        ws.Range("H:H").EntireColumn.Insert
        
        ws.Range("H1").Value = "TOTALPAYMENTS"
        
        'ws.Columns("J").Delete
        'ws.Columns("K").Delete
        'ws.Columns("L:O").Delete
        
        successrng = ws.Cells.Find(What:="Success", After:=ActiveCell, LookIn:=xlValues _
                     , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                     MatchCase:=False).Row
        
        For Each cell In successrange '<-- error popping out from this line
            
            If Not cell Is Nothing Then
                
                ws.Rows(successrng).EntireRow.Delete
                
            End If
            
        Next cell
        
        For i = 2 To lastRow
            
            Cells(i, 8).Value = Cells(i, 6).Value * Cells(i, 7).Value
            
        Next i
        
    Next
    
End Sub

[#4]

@hrh_dash 

You assign a value to the variable successrng (a row number), then use the variable successrange (a Range) without having set it to anything.

In which column do you want to look for "Success"? A specific column, or all columns?

[#5]

Hi @Hans Vogelaar , i amended the code as follows:

    Dim cell        As Range
    Dim successrange As Range

Set successrange = ws.Range("R2:R" & lastRow)
        
        For Each cell In successrange
                   
            If cell.Value = "Success" Then
                            
                cell.EntireRow.Delete
                
            End If
            
        Next cell

seems like the .Find function wasn't useful for this case. 

The code works but it only deleted 2 rows of "Success" instead of 3 rows. Not sure why it did not delete all 3 rows. 

If originally there are 4 rows of "Success", it should delete all 4 instead of 2 rows..

[#6 · Accepted solution]

@hrh_dash 

Try this:

Sub daily_installment()
    Dim ws           As Worksheet
    Dim lastRow      As Long
    Dim i            As Long
    Dim successrange As Range
    For Each ws In Worksheets
        ws.Rows("1").EntireRow.Delete
        ws.Columns("A:A").TextToColumns Destination:=ws.Range("A1"), DataType:=xlDelimited, _
                                        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                                        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:=";"
        lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
        ws.Rows(lastRow).EntireRow.Delete
        ws.Range("H:H").EntireColumn.Insert
        ws.Range("H1").Value = "TOTALPAYMENTS"
        Set successrange = ws.Cells.Find(What:="Success", LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
        Do While Not successrange Is Nothing
            successrange.EntireColumn.Delete
            Set successrange = ws.Cells.Find(What:="Success", LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
        Loop
        lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).EntireRow.Row
        With ws.Range("H2:H" & lastRow)
            .Formula = "=F2*G2"
            .Value = .Value
        End With
        ws.Range("A1").CurrentRegion.EntireColumn.AutoFit
    Next ws
End Sub

[#7]

Thanks for the assist, much appreciated. The code works perfectly.

May I ask:
1. Why is there 2 lines of code needed for lastRow and

2. What is the difference between lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
and
lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Entirerow.Row?

I thought both of them are the same thing, therefore having the Entirerow or not the code should run smoothly. Sorry for asking, would like to clear my doubts on this..

[#8]

@hrh_dash 

1. I inserted the second line for lastRow because the original value isn't valid anymore: we have deleted some rows since the value was first calculated.
2. EntireRow isn't needed in that line. The row number of an entire row is exactly the same as the row number of any cell in that row.

[#9]

thanks and appreciate the explanation!