Find value in another Sheet and follow a pattern

[urubag]

Hi Guys,

Please note I am trying to do a friendly search for the customers since the structure of the report its complicated.
Basically, I need to find a value and then follow some patterns already identified and fill the table based on the values sheet1 (D2:D10).


The code is not completed because basically I am stuck in the part to simulate CTRL + F but in a sheet (YODA2) running macro from Sheet1.
The value to find it can be in any column.

Sub FindYoda2()

Dim cell As Range
Dim ID As String

Sheet1.Select
Range("D1").Select

For Each cell In Range("D2:D11")

    ActiveCell.Offset(1, 0).Select 'Value to find
    
        ID = ActiveCell.Value
        
          Sheets("YODA2").Column("A:BZ").Find(What:=ID, After:=ActiveCell, LookIn:=xlFormulas2, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
                
                Selection.End(xlUp).Select
                
                ActiveCell.Offset(0, 5).Copy 'CC Name
                ActiveCell.Offset(-2, -1).Copy 'Node
                ActiveCell.Offset(0, 8).Copy 'Node Name
            
                
                'paste each value in the respective column sheet1
        
Next

        
      
End Sub

Note: Images attached for your reference, since I was not able to attach the file.

Thanks in advance
Andres

 

[James006]

Hi,

You seem to really over-complicate things ... especially with all your 'Select' instructions and 'ActiveCell' which are not necessary at all ...

Before diving into your loop for several cells, do you have a simple search for a single cell working as expected ... ?

 

[bebo021999]

The below code works for me:

Option Explicit
Sub FindYoda2()
Dim i&, j&, c As Boolean, lr&, res, src
With Sheets("YODA2")
    lr = .Cells(Rows.Count, "F").End(xlUp).Row
    src = .Range("E4:M" & lr).Value
End With
With Sheets("Sheet1")
    lr = .Cells(Rows.Count, "D").End(xlUp).Row
    res = .Range("D2:G" & lr).Value
    For i = 1 To UBound(res)
        c = False
        For j = 1 To UBound(src)
            If Not IsEmpty(src(j, 1)) Then
                res(i, 3) = src(j, 1): res(i, 4) = src(j, 9)
            End If
            If res(i, 1) = src(j, 2) Then
                res(i, 2) = src(j, 7)
                c = True
                Exit For
            End If
        Next
        If Not c Then
            res(i, 3) = "Not found": res(i, 4) = "Not found"
        End If
    Next
    .Range("E2:G10000").ClearContents
    .Range("D2:G" & lr).Value = res
End With
End Sub

 

[urubag]

Hi,

You seem to really over-complicate things ... especially with all your 'Select' instructions and 'ActiveCell' which are not necessary at all ...

Before diving into your loop for several cells, do you have a simple search for a single cell working as expected ... ?

Thanks for the feedback I don't ask about it , I am still learning, and I try to create macros functional not necessarily quickly.

Basically, it's a simple search in different sheet, needs to be a search because the value to find can be in any column of the sheet.

 

[urubag]

The below code works for me:

Option Explicit
Sub FindYoda2()
Dim i&, j&, c As Boolean, lr&, res, src
With Sheets("YODA2")
    lr = .Cells(Rows.Count, "F").End(xlUp).Row
    src = .Range("E4:M" & lr).Value
End With
With Sheets("Sheet1")
    lr = .Cells(Rows.Count, "D").End(xlUp).Row
    res = .Range("D2:G" & lr).Value
    For i = 1 To UBound(res)
        c = False
        For j = 1 To UBound(src)
            If Not IsEmpty(src(j, 1)) Then
                res(i, 3) = src(j, 1): res(i, 4) = src(j, 9)
            End If
            If res(i, 1) = src(j, 2) Then
                res(i, 2) = src(j, 7)
                c = True
                Exit For
            End If
        Next
        If Not c Then
            res(i, 3) = "Not found": res(i, 4) = "Not found"
        End If
    Next
    .Range("E2:G10000").ClearContents
    .Range("D2:G" & lr).Value = res
End With
End Sub

Thanks for the reply, not works properly because you are finding the values in specific column and the value to find can be in any column of Sheet (YODA2).
The code should work as you manually do a CTRL + F in the sheet YODA2 but the thing is the macro will be executed from Sheet1.

Appreciate at least if someone can help me with the search part.

Regards