Pop up error: run time error 438 object doesn't support this property or method

[hrh_dash]

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!

[#2]

@hrh_dash 

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.

[#3]

@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.

[#4]

@hrh_dash 

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)

.

[#5]

@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

[#6 · Accepted solution]

@hrh_dash 

You should use lines such as

ws.Range("H" & miscstartRow & ":H" & misclastRow).Copy

[#7]

thank you for the help!

[#8]

@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 If

1st run

2nd run

[#9]

@hrh_dash 

I'd have to see a copy of the workbook.

[#10]

@HansVogelaar , excel workbook attached. The code is placed under Worksheet "Count". It would be under Sub counter().

Thank you and appreciate the assistance.

[#11]

@hrh_dash 

Thanks. It's in Sub MISC_DC1_DC2(), by the way.

The cause of the behavior that you describe is because you use After:=ActiveCell in the call to ws1.Cells.Find.

The first time, DC2 is found in C5. The second time, it is found after that cell, so in I5.

If you remove the After part, it'll always find DC2 in C5:

    Set DestDC2 = ws1.Cells.Find(What:="DC2", LookIn:=xlValues _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)

If you always want to find the value in column C, use ws1.Range("C:C").Find(...)

If you always want to find the value in column I, use ws1.Range("I:I").Find(...)

[#12]

@HansVogelaar , apologies for the wrong sub.. thanks for the explanation. Learnt quite a fair bit from you.

By the way, is it possible to execute a for each loop to .find(What:="DC2") and add up all the values that one column next to "DC2" cell?

For example, it will be coded as follows:

Dim DestDC2 as Range
Dim ws1 as Worksheet
Dim ws2 as Worksheet

Set ws1 = Sheet1
set ws2 = Sheet2

Set DestDC2 = ws2.Cells.Find(What:="DC2", After:=ActiveCell, LookIn:=xlValues _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)

For each cell in DestDC2

ws1.range("A1") = Application.WorksheetFunction.Sum (DestDC2.offset(0, 1))

Next cell

[#13]

@hrh_dash 

Find returns the first matching cell, so DestDC2 is a single cell.

Instead of the For Each ... Next loop, you can use

ws1.Range("A1").Value = Application.SumIf(ws2.Range("J:J"), ws2.Range("I:I"), "DC2*")

[#14]

i have got another project whereby i need to use the .Find function. "DC2" could be in any column but the next column is always the values. Therefore, i would like to use a for loop to loop through every cells that contains "DC2" and sum up to find the total values under "DC2".

[#15]

@hrh_dash 

Do you want to look for cells whose value is DC2 without other text, or for cells whose value contains DC2 with possibly other text?

[#16]

, it would be cells which contains DC2 with other text

[#17]

@hrh_dash 

    Dim DestDC2 As Range
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim adr As String
    Dim sumDC2 As Double

    Set ws1 = Sheet1
    Set ws2 = Sheet2
    Set DestDC2 = ws2.Cells.Find(What:="DC2", LookAt:=xlPart)
    If Not DestDC2 Is Nothing Then
        adr = DestDC2.Address
        Do
            sumDC2 = sumDC2 + DestDC2.Offset(0, 1).Value
            Set DestDC2 = ws2.Cells.Find(What:="DC2", After:=DestDC2, LookAt:=xlPart)
            If DestDC2 Is Nothing Then Exit Sub
        Loop Until DestDC2.Address = adr
    End If
    ws1.Range("A1") = sumDC2

[#18]

,thank you! the code works perfectly what i wanted. Thanks for the assistance