If string match found place formula in corresponding cell column U

[redspanna]

Hey all

Can you help with code that will look down column B (starting at B3) of the 'Stock' sheet and try to find an exact match found through column T (starting at T13) in the 'StockList' sheet.
If match found then place formula =VLOOKUP(B*,StockList!T13:BA4,5,False) in corresponding row column U of "stock" sheet.

Loop until all values in column B of Stock sheet have been checked.

For example if RED DOG is found in B6, check this string through column T of the StockList sheet - if a match is found there, then place the formula
=VLOOKUP(B6,StockList!T13:BA4,5,False) in cell U6 of the stock sheet

hope this makes sense and thanks in advance

 

[DanteAmor]

In the StockList sheet, only search in range T4:BA13?
If there are more rows you can change the number of rows to for example:

T4:BA10000

Or Simply:
T:BA

Try:

Sub place_formula()
  Application.ScreenUpdating = False
  With Sheets("Stock").Range("U3:U" & Sheets("Stock").Range("B" & Rows.Count).End(3).Row)
    .Formula = "=VLOOKUP(B3,StockList!T4:BA13,5,0)"
    On Error Resume Next
    .SpecialCells(xlCellTypeFormulas, xlErrors).ClearContents
  End With
  Application.ScreenUpdating = True
End Sub

The above macro puts the formula only if it found the data, but if you want the formulas in all cells regardless of the result, then delete these lines:

    On Error Resume Next
    .SpecialCells(xlCellTypeFormulas, xlErrors).ClearContents

If you want the formula in all cells but don't want to display the #N/A error then use the following:

Sub place_formula()
  With Sheets("Stock").Range("U3:U" & Sheets("Stock").Range("B" & Rows.Count).End(3).Row)
    .Formula = "=IFNA(VLOOKUP(B3,StockList!T4:BA13,5,0),"""")"
  End With
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[DanteAmor]

The previous macros do not use cycles, but if you prefer a cycle, I give you the option:

Sub If_string_match_found_place_formula()
  Dim c As Range, f As Range
  Dim sh1 As Worksheet
  Set sh1 = Sheets("Stock")
  For Each c In sh1.Range("B3", sh1.Range("B" & Rows.Count).End(3))
    Set f = Sheets("StockList").Range("T:T").Find(c.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      sh1.Range("U" & c.Row).Formula = "=VLOOKUP(B" & c.Row & ",StockList!T1:BA" & f.Row & ",5,0)"
    End If
  Next
End Sub

By the way, the range up to column BA is not necessary, since you only need column 5, so the range T to X is enough:

"=VLOOKUP(B" & c.Row & ",StockList!T1:X" & f.Row & ",5,0)"

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[redspanna]

The previous macros do not use cycles, but if you prefer a cycle, I give you the option:

Sub If_string_match_found_place_formula()
  Dim c As Range, f As Range
  Dim sh1 As Worksheet
  Set sh1 = Sheets("Stock")
  For Each c In sh1.Range("B3", sh1.Range("B" & Rows.Count).End(3))
    Set f = Sheets("StockList").Range("T:T").Find(c.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      sh1.Range("U" & c.Row).Formula = "=VLOOKUP(B" & c.Row & ",StockList!T1:BA" & f.Row & ",5,0)"
    End If
  Next
End Sub

By the way, the range up to column BA is not necessary, since you only need column 5, so the range T to X is enough:

"=VLOOKUP(B" & c.Row & ",StockList!T1:X" & f.Row & ",5,0)"

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

That's perfect and thanks for the detailed explanation too - really helpful !