Three With blocks

[watzmann]

I have one Sub contains two " with activesheet " blocks and they are working fine but when i add this new block i have got an error , i don't know may be this code is wrong.

With ActiveSheet

Cells.Find(What:="area", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Cells.findnext(After:=ActiveCell).Activate
found.Copy Destination:="AD1300"

End With

i'm tryin to find a specific word and if found to copy it to a specific cell. ,

i got this error when i run the code" Application-defined or object defined error "

[watzmann]

this is my full code after some modification but it still doesn't work with te last block

With ActiveSheet
    On Error Resume Next
    Set found1 = .Cells.Find(What:="ASK Training Managers", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False)
    On Error GoTo 0
            
    ' Make sure that something was found1
    If found1 Is Nothing Then Exit Function
    
    first_found_addr = found1.Address
    
    ' Loop until we either come back to the first cell we found1, or for some reason we can't find anything
    Do
        ' Copy from an offset of (-5,2) to "AD20" list
        If .Range("AC1122") = "" Then ' If AD20 is blank then put the result there, otherwise add to the list
            Set dest_rng1 = Range("AC1122")
        ElseIf .Range("AC1123") = "" Then
            Set dest_rng1 = Range("AC1123")
        Else
            Set dest_rng1 = .Range("AC1122").End(xlDown).Offset(1)
        End If
        found1.Offset(-5, -17).Copy Destination:=dest_rng1
        ' Find the next occurance and set 'found1' to be that cell
        Set found1 = .Cells.findnext(found1)
    Loop While found1.Address <> first_found_addr
    
    
    
   
End With



With ActiveSheet

Cells.Find(What:="Total:", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate

    Selection.Copy
    Range("G1200").Select
    ActiveSheet.Paste
    
    End With

[piyushdabomb]

Where is the error exactly? It works for me.

A quick note on writing VBA macros, always 'fully' qualify your code.

For example, rather than writing, Cells.Find(...), reference which workbook and sheet you are referring to.

So for example, you start with:

"With Activesheet"...

Instead, why not write this instead?:

With "Thisworkbook.Activesheet"...

[Leith Ross]

Hello watzmann,

The Find method will return either the cell where the match was found or Nothing. You should Set a range object equal to the Find result. Use and If Then to determine if Find was sucessfull.

Example

With ActiveSheet

  Set Found1 = Cells.Find(What:="Total:", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)

  If Not Found1 Is Nothing Then
    Selection.Copy
    Range("G1200").Select
    ActiveSheet.Paste
  End If  

End With