Hi All,

I've stumbled into an issue while trying to use a text formula in VBA to set the value of certain cells. Hypothetically I could insert a formula into a cell then paste as value to get the same result, however I am working with a huge document and trying to write the most efficient code possible.

Here is the code I've written, with the understanding that the "v = ..." part of the code isn't correct, but I wanted to show the formula I need to use there. In that formula, the cell "B2" should be dynamic such that it is "B"&i.

Please let me know if you have any solutions or thoughts.


Thanks in advance.

Sub Test()



Dim R As Long, v As String

R = Range("B2").End(xlDown).Row


For i = 2 To R

v = IF(COUNT(SEARCH({"ib",";iber","iiber","iber","ibir"},B2)),"Cat1",IF(ISNUMBER(SEARCH("Unavailable",B2)),"Cat2","Cat3"))


Range("G" & i).Value = v


Next i



End Sub