CODEWITHSUNDEEP
excelvba
aj_bk
aj_bk

Reputation: 194

Find a value in range + loop

I need my macro to Look at a cell in my range, Find that value in the a different WS and paste a value on to that's next to the value i'm looking for (my original WS). do this again and again to the values in the range.

now it all works but for some reason the value is stuck on the first search and wont look for other values in the original range. here is my code, and the pictures should help.

Sub Macro1()

' 'now im gonna match the "UDD" TO THE "S/O"

Worksheets("Sheet1").Activate
Range("c17").Select

   Dim Searchkey As Range, cell As Range
   Set Searchkey = Range("c17:c160")

For Each cell In Searchkey
Sheets("data").Activate
Cells.Find(What:=Searchkey, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate
ActiveCell.Offset(0, -1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Activate
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, -1).Range("A1").Select
Selection.Copy
Next cell

End Sub

why is my macro stuck on "84225" and not looping to the other S/O?

Thank youenter image description here

Upvotes: 0

Views: 926

Answers (2)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19712

On each loop you're searching for the whole range of SearchKey and not just Cell so I'm guessing it's always using the first cell in SearchKey as your search criteria.

You're also searching in the formula rather than the values, and looking for a part match which may return incorrect results (part match on 20 would return a find in 20, 201, 11120001, etc).

Not qualifying your sheet names and using Activate probably isn't helping much either.

Try this code:

Public Sub Test()

    Dim SrcSht As Worksheet, TgtSht As Worksheet
    Dim SearchKey As Range, Cell As Range
    Dim FoundValue As Range

    With ThisWorkbook
        Set SrcSht = .Worksheets("Sheet1")
        Set TgtSht = .Worksheets("Data")
    End With

    Set SearchKey = SrcSht.Range("C17:C21")
    For Each Cell In SearchKey
        'Search column 3 (C) for your the value
        Set FoundValue = TgtSht.Columns(3).Find(What:=Cell, _
                                                After:=TgtSht.Columns(3).Cells(1, 1), _
                                                LookIn:=xlValues, _
                                                LookAt:=xlWhole, _
                                                SearchOrder:=xlByRows, _
                                                SearchDirection:=xlNext)

        'Only proceed if value found, otherwise an error will occur.
        If Not FoundValue Is Nothing Then
            Cell.Offset(, 1) = FoundValue.Offset(, 1)
        End If
    Next Cell

End Sub

Edit:
To test the code place the cursor within the procedure and press F8 to process each line in turn. The FoundValue should contain a value each time it's executed.

To check this hover your cursor over the variable to see its value:

enter image description here

The row highlighted in yellow is the next line that will be executed. If FoundValue is nothing then the following line isn't processed, if it's not nothing then the line is executed.

Upvotes: 0

aj_bk
aj_bk

Reputation: 194

Sub mac1()


Worksheets("Sheet1").Activate
Range("c17").Select


Dim srch As Range, cell As Variant
Set srch = Range("c17:c160")

For Each cell In srch
Sheets("data").Activate

    Cells.Find(What:=cell, LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate

ActiveCell.Offset(0, -1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Activate
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, -1).Range("A1").Select
Selection.Copy
Next cell

End Sub

this is working! thank you all

Upvotes: 1

Related Questions