CODEWITHSUNDEEP
arraysvbaexcel
user2965077
user2965077

Reputation: 87

VBA Find Formula

I am using the following code to search in the A column of a row for a name. If the name is found, it is placed in a column 2 over. I am trying to search against a list of names rather than one name. The names are listed in sheet1, I am searching text stored in column A on sheet4. Each row has a paragraph of text I want to search. When a match is found, the matching name(s) is put in cell c of the same row.

   Sub test()

Dim ws1, ws2 As Worksheet, rng1, rng2, cel1, cel2 As Range
Dim i, lrow As Long

Set ws1 = ThisWorkbook.Sheets("Sheet1")
Set ws2 = ThisWorkbook.Sheets("Sheet4")

'i only assumed that your data is both in column A of sheet 1 and 2
lrow = ws1.Range("A" & Rows.Count).End(xlUp).Row
Set rng1 = ws1.Range("A1:A" & lrow) 'this contains the names
lrow = ws2.Range("A" & Rows.Count).End(xlUp).Row
Set rng2 = ws2.Range("A1:A" & lrow) 'this contains list of text you want to search

i = 0
For Each cel2 In rng2
    For Each cel1 In rng1
        If InStr(cel1.Value, cel2.Value) <> 0 Then cel1.Copy ws2.Range("c1").Offset(i, 0): i = i + 1
    Next cel1
Next cel2

End Sub

Cheers!

Upvotes: 0

Views: 175

Answers (2)

L42
L42

Reputation: 19737

this is another way to get what you want but not really using formula.

Option Explicit

Sub test()

Dim ws1, ws2 As Worksheet, rng1, rng2, cel1, cel2 As Range
Dim i, lrow As Long

Set ws1 = ThisWorkbook.Sheets("Sheet1")
Set ws2 = ThisWorkbook.Sheets("Sheet2")

'i only assumed that your data is both in column A of sheet 1 and 2
lrow = ws1.Range("A" & Rows.Count).End(xlUp).Row
Set rng1 = ws1.Range("A1:A" & lrow) 'this contains the names
lrow = ws2.Range("A" & Rows.Count).End(xlUp).Row
Set rng2 = ws2.Range("A1:A" & lrow) 'this contains list of text you want to search

i = 0
For Each cel2 In rng2
    For Each cel1 In rng1
        If InStr(cel1.Value, cel2.Value) <> 0 Then cel1.Copy ws1.Range("B1").Offset(i, 0): i = i + 1
    Next cel1
Next cel2

End Sub

I proposed above approach since you are open to using VBA.
hope this is what or somewhat close to what you want.

Upvotes: 1

user2140261
user2140261

Reputation: 7993

If my comment is correct then this should work for you:

say I start with this set up:

enter image description here

I would first add my look up values to a named range as follows:

enter image description here

then you can add this code: 

Sub Sample()
    Application.ScreenUpdating = False
    With Range("A2", Range("A" & Rows.Count).End(xlUp)).Offset(, 2)
        .FormulaR1C1 = _
        "=IFERROR(LOOKUP(1E+100,SEARCH(LookUpValues,RC[-2]),LookUpValues),"""")"
        .Value = .Value
    End With
    Application.ScreenUpdating = True
End Sub

and this should result in the following:

enter image description here

Upvotes: 1

Related Questions