Forum Discussion

Iron Contributor

Aug 11, 2022

Solved

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

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 _ ...

excel

Formulas and Functions

Macros and VBA

HansVogelaar

Aug 12, 2022

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

HansVogelaar

MVP

Aug 11, 2022

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)

            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.

hrh_dash

Iron Contributor

Aug 12, 2022

HansVogelaar , 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

HansVogelaar

MVP

Aug 12, 2022

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?

hrh_dash

Iron Contributor

Aug 12, 2022

Hi HansVogelaar , 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..

HansVogelaar

MVP

Aug 12, 2022

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