Forum Discussion
Error pop up: compile error sub or function not definedwhen executing macro
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
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.
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 SubYou 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?
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 cellseems 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..
