Forum Discussion
Solved
Pop up error: run time error 438 object doesn't support this property or method
Pop up error: "run time error 438 object doesn't support this property or method" when finding the first row and the last row containing specific text as for in this case would be "DC2".
I would like to
1. find the last row containing specific text "DC2" in column A in destination ws(Destws)
2. find the first row containing specific text "DC2" in column I in source ws (ws)
3. find the last row containing specific text "DC2" in column I in source ws (ws)
4. copy rows from ws in to Destws if "DC2" exists in Destws
this is my code so far..
Sub findMISC()
Dim ws As Worksheet
Dim Destwb As Workbook
Dim Destws As Worksheet
Dim wslastRow As Long
Dim DestlastRow As Long
Dim shtno As String
Dim miscstartRow As Long
Dim misclastRow As Long
Dim Destmisc As Range
Dim DestlastRow2 As Long
Set ws = ThisWorkbook.Sheets("TCA")
shtno = InputBox("Please indicate Sheet name to paste data into:")
Set Destwb = Workbooks.Open("C:\Users\hrhquek\Desktop\DEBT RECOVERY\testing.xlsx")
Set Destws = Destwb.Sheets(shtno)
Set Destmisc = Destwb.Sheets(shtno).Find(What:="DATE OF LAST AR", After:=ActiveCell, LookIn:=xlValues _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
miscstartRow = ws.Range("I:I").Find(What:="DC2", After:=ws.Range("I2")).Row '<--- error starting from this line of code
misclastRow = ws.Range("I:I").Find(What:="DC2", After:=ws.Range("I2"), SearchDirection:=xlPrevious).Row
DestlastRow = Destws.Range("A:A").Find(What:="DC2", After:=Destws.Range("A2"), SearchDirection:=xlPrevious).Row
If Not Destmisc Is Nothing Then
ws.Range("I2:I" & miscstartRow & misclastRow).Copy
Destws.Range("A" & DestlastRow).PasteSpecial xlValues
ws.Range("D2:D" & miscstartRow & misclastRow).Copy
Destws.Range("C" & DestlastRow).PasteSpecial xlValues
Destws.Range("C:C").NumberFormat = "0.00000000"
ws.Range("E2:E" & miscstartRow & misclastRow).Copy
Destws.Range("D" & DestlastRow).PasteSpecial xlValues
ws.Range("F2:F" & miscstartRow & misclastRow).Copy
Destws.Range("E" & DestlastRow).PasteSpecial xlValues
ws.Range("G2:G" & miscstartRow & misclastRow).Copy
Destws.Range("F" & DestlastRow).PasteSpecial xlValues
ws.Range("H2:H" & miscstartRow & misclastRow).Copy
Destws.Range("G" & DestlastRow).PasteSpecial xlValues
Destws.Range("G:G").NumberFormat = "$#,##0.00_);($#,##0.00)"
Else
wslastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).EntireRow.Row
DestlastRow2 = Destws.Cells(Destws.Rows.Count, "B").End(xlUp).Offset(1).Row
ws.Range("I2:I" & wslastRow).Copy
Destws.Range("A" & DestlastRow2).PasteSpecial xlValues
ws.Range("D2:D" & wslastRow).Copy
Destws.Range("C" & DestlastRow2).PasteSpecial xlValues
Destws.Range("C:C").NumberFormat = "0.00000000"
ws.Range("E2:E" & wslastRow).Copy
Destws.Range("D" & DestlastRow2).PasteSpecial xlValues
ws.Range("F2:F" & wslastRow).Copy
Destws.Range("E" & DestlastRow2).PasteSpecial xlValues
ws.Range("G2:G" & wslastRow).Copy
Destws.Range("F" & DestlastRow2).PasteSpecial xlValues
ws.Range("H2:H" & wslastRow).Copy
Destws.Range("G" & DestlastRow2).PasteSpecial xlValues
Destws.Range("G:G").NumberFormat = "$#,##0.00_);($#,##0.00)"
End If
End Sub
appreciate the assistance in advance!
In a quick test, those lines appear to work as intended, so it's impossible to tell what causes the error without seeing a copy of your workbook.
HansVogelaar , have attached the 2 workbooks; BSCS Outsource Macro (will be file containing the code and also the source file) and testing (destination file).
Appreciate the assistance.
Thanks I get error 438 on this instruction:
Set Destmisc = Destwb.Sheets(shtno).Find(What:="DATE OF LAST AR", After:=ActiveCell, LookIn:=xlValues _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False)This is because Find is a method of the Range object, not of the Worksheet object.
But if you change it to
Set Destmisc = Destws.Cells.Find(What:="DATE OF LAST AR", After:=ActiveCell, LookIn:=xlValues _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False)then Destmisc will be Nothing since the (only) sheet in Testing.xlsx does not contain the text DATE OF LAST AR.
After that, the line
miscstartRow = ws.Range("I:I").Find(What:="DC2", After:=ws.Range("I2")).Rowwill fail since the text DC2 is not found in column I of the TCA sheet. (It is in column B)
.
