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 ...
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")).Row
will 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- thank you for the help!
