Forum Discussion
Pop up error: run time error 438 object doesn't support this property or method
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)
.
HansVogelaar , thanks for the advice. I have made some changes to the code and there is no error.
However, the copy and paste portion did not go quite well.
If i were to Find: "DC2";
I would like the copy and paste final result to be as follows:
This is the original state before the macro runs:
This is the result after the macro is executed:
Amended code so far:
Set Destwb = Workbooks.Open("C:\Users\hrhquek\Desktop\DEBT RECOVERY\testing.xlsx") Set Destws = Destwb.Sheets(shtno) Set Destmisc = Destws.Cells.Find(What:="DC2", After:=ActiveCell, LookIn:=xlValues _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) miscstartRow = ws.Range("H:H").Find(What:="DC2", After:=ws.Range("H2")).Row misclastRow = ws.Range("H:H").Find(What:="DC2", After:=ws.Range("H2"), SearchDirection:=xlPrevious).Row DestlastRow = Destws.Range("B:B").Find(What:="DC2", After:=Destws.Range("B2"), SearchDirection:=xlPrevious).Row If Not Destmisc Is Nothing Then ws.Range("H2:H" & miscstartRow & misclastRow).Copy '<-- is this line coded correctly? Destws.Range("B" & DestlastRow).Offset(1).PasteSpecial xlValues ws.Range("C2:C" & miscstartRow & misclastRow).Copy '<-- is this line coded correctly? Destws.Range("C" & DestlastRow).Offset(1).PasteSpecial xlValues Destws.Range("C:C").NumberFormat = "0.00000000" ws.Range("D2:D" & miscstartRow & misclastRow).Copy '<-- is this line coded correctly? Destws.Range("D" & DestlastRow).Offset(1).PasteSpecial xlValues ws.Range("E2:E" & miscstartRow & misclastRow).Copy '<-- is this line coded correctly? Destws.Range("E" & DestlastRow).Offset(1).PasteSpecial xlValues ws.Range("F2:F" & miscstartRow & misclastRow).Copy '<-- is this line coded correctly? Destws.Range("F" & DestlastRow).Offset(1).PasteSpecial xlValues ws.Range("G2:G" & miscstartRow & misclastRow).Copy '<-- is this line coded correctly? Destws.Range("G" & DestlastRow).Offset(1).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("H2:H" & wslastRow).Copy Destws.Range("A" & DestlastRow2).PasteSpecial xlValues ws.Range("C2:C" & wslastRow).Copy Destws.Range("C" & DestlastRow2).PasteSpecial xlValues Destws.Range("C:C").NumberFormat = "0.00000000" ws.Range("D2:D" & wslastRow).Copy Destws.Range("D" & DestlastRow2).PasteSpecial xlValues ws.Range("E2:E" & wslastRow).Copy Destws.Range("E" & DestlastRow2).PasteSpecial xlValues ws.Range("F2:F" & wslastRow).Copy Destws.Range("F" & DestlastRow2).PasteSpecial xlValues ws.Range("G2:G" & wslastRow).Copy Destws.Range("G" & DestlastRow2).PasteSpecial xlValues Destws.Range("G:G").NumberFormat = "$#,##0.00_);($#,##0.00)" End If
