Forum Discussion
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 _ ...
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
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..
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- 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..- 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.
- 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.
- thanks and appreciate the explanation!