Find Value

[RLJ]

I have a spreadsheet that In Column A, I want to Look up instances where the cell has a value of ABC123 and then run a Copy Data Code and return to look up the next instance of ABC123, but the code is loosing ABC123 and not going through all instances of it. I made some comments in my code below where it is breaking.

Sub Trial()


    Sheets("Deal Tracker").Select
    
    With Worksheets("Deal Tracker").Range("a1:a500")
     Set c = .Find("ABC123", LookIn:=xlValues)
     If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Select
            CopyData
            Set c = .FindNext(c) 'When the code gets to this point, c is still = "ABC123"
        If c Is Nothing Then     'But immediately after c goes to "Nothing" even though there are still more instances of "ABC123"
            GoTo DoneFinding
        End If
        Loop While c.Address <> firstAddress
      End If
DoneFinding:
End With
End Sub


Sub CopyData()

    Dim lookVal As String


    ActiveCell.Offset(0, 1).Select
    lookVal = ActiveCell.Value 'Property Name
    
    Sheets("Import Temp").Select 'Main Sheet that is being Updated
    
    Cells.Find(What:=lookVal, After:=ActiveCell, LookIn:= _
        xlFormulas, lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
        Range("C" & (ActiveCell.Row)).Select
    
    ActiveCell.Offset(0, 9).Copy
    
    
    Sheets("Deal Tracker").Select 'Main Sheet where data is being copied
    ActiveCell.Offset(0, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks _
        :=False, Transpose:=False
        
    Sheets("Import Temp").Select 'Main Sheet that is being Updated
    ActiveCell.Offset(0, 10).Copy


    
    Sheets("Deal Tracker").Select 'Main Sheet where data is being copied
    ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks _
        :=False, Transpose:=False
        
    Sheets("Import Temp").Select 'Main Sheet that is being Updated
    ActiveCell.Offset(0, 19).Copy


    
    Sheets("Deal Tracker").Select 'Main Sheet where data is being copied
    ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks _
        :=False, Transpose:=False
        
    Sheets("Import Temp").Select 'Main Sheet that is being Updated
    ActiveCell.Offset(0, 20).Copy


    
    Sheets("Deal Tracker").Select 'Main Sheet where data is being copied
    ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, -6).Select


End Sub

I'm lost as to why this is happening. I'm really hoping this can be solved.

Thanks for all your help.

 

[Fluff]

It's because you are using .Find in the CopyData macro.
As FindNext repeats the previous Find its looking for lookVal, not ABC123

 

[RLJ]

Can I change the code so that it can still look for ABC123?

 

[Fluff]

Try

Sub Trial()
   Dim Fnd As Range
   Dim i As Long, Qty As Long

    
   Worksheets("Deal Tracker").Select
   
   Set Fnd = Range("a1")
   Qty = Application.CountIf(Range("a1:a500"), "Devon")
   For i = 1 To Qty
      Set Fnd = Range("a1:a500").find("Devon", Fnd, , , , xlNext, False, , False)
      Fnd.Select
      CopyData
   Next i
End Sub

 

[RLJ]

This looks like it is working just as I need.

 

[Fluff]

Glad to help & thanks for the feedback

 

[RLJ]

It did work out great. Thanks