Aug 10 2022 06:57 PM
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..
Aug 11 2022 12:09 AM
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.
Aug 11 2022 06:16 PM
@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
Aug 12 2022 02:02 AM
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?
Aug 12 2022 08:27 AM
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..
Aug 12 2022 12:35 PM
SolutionTry 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
Aug 13 2022 08:28 AM
Aug 13 2022 08:35 AM
Aug 15 2022 06:20 AM
Aug 12 2022 12:35 PM
SolutionTry 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