Forum Discussion
Pop up error: run time error 438 object doesn't support this property or method
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!
HansVogelaar , i have a query regarding on this thread as it is somehow similar.
I am trying to create this macro code to find if DC2 is found in Sheets("TCA"), if yes, it would copy and paste the batch id into my source worksheet ("Count").
So when i execute the code the first time, it populates "DC2" in my source worksheet ("Count"). However, when i execute the code the 2nd time, it populates the batch id into my source worksheet.
Seems like the code needs to run twice to capture the batch id in Sheets("TCA") which is quite weird. Not sure what is wrong, i don't have any issues with the rest as the code is the same just amending the variables.
the code as follows:
Dim ws As Worksheet Dim ws1 As Worksheet Dim DestDC1 As Range Dim DestDC2 As Range Dim Destmisc As Range Set ws = ThisWorkbook.Sheets("Count") Set ws1 = ThisWorkbook.Sheets("TCA") Set Destmisc = ws1.Cells.Find(What:="MISC", After:=ActiveCell, LookIn:=xlValues _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) Set DestDC1 = ws1.Cells.Find(What:="DC1", After:=ActiveCell, LookIn:=xlValues _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) Set DestDC2 = ws1.Cells.Find(What:="DC2", After:=ActiveCell, LookIn:=xlValues _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) If ws.Range("B3").Value = 0 Then ws.Range("B5").Value = "" Else Destmisc.Copy ws.Range("B5").PasteSpecial Paste:=xlPasteValues End If If ws.Range("C3").Value = 0 Then ws.Range("C5").Value = "" Else DestDC1.Copy ws.Range("C5").PasteSpecial Paste:=xlPasteValues End If If ws.Range("D3").Value = 0 Then ws.Range("D5").Value = "" Else DestDC2.Copy ws.Range("D5").PasteSpecial Paste:=xlPasteValues End If1st run
2nd run
I'd have to see a copy of the workbook.