Add a formula with concatenate in a range of cells using VBA

[jhonty4]

Hi, I am trying to put this formula " ="*"&MID(B7,SEARCH("(",B7)+1,SEARCH(")",B7)-SEARCH("(",B7)-1)&"*" " in a range of cell from row 7 to lastrow(Column E) using VBA code. but cannot figure out how to do that using VBA because concatenate is involved.

For k = 7 To lnglastrow
        Range("G" & k).Formula ="=MID(B7,SEARCH("(",B7)+1,SEARCH(")",B7)-SEARCH("(",B7)-1)"
next k

The above code needs to be changed such that the range will be updated with the formula mentioned above. TIA

 

[MARK858]

Try

Range("G7:G" & Range("E" & Rows.Count).End(xlUp).Row).FormulaR1C1 = _
        "=MID(RC[-5],SEARCH(""("",RC[-5])+1,SEARCH("")"",RC[-5])-SEARCH(""("",RC[-5])-1)"

or

Range("G7:G" & Range("E" & Rows.Count).End(xlUp).Row).Formula = _
        "=MID(B7,SEARCH(""("",B7)+1,SEARCH("")"",B7)-SEARCH(""("",B7)-1)"

because concatenate is involved

 

[gallen]

You don't need to loop, just Autofill:

Note the triple quotation marks """ to insert a quotation mark into a string

Sub SetFormula()
Dim sFormula As String
Dim lngLastRow As Long 'Last row


    sFormula = "=MID(B7,SEARCH(" & """(""" & ",B7)+1,SEARCH(" & """)""" & ",B7)-SEARCH(" & """(""" & ",B7)-1)"

'insert code to set lngLastRow. I wasn't sure which column you use to determine last row

    Range("G7").Formula = sFormula
    Range("G7").AutoFill Range("G7:G" & lngLastRow)
    
End Sub

 

[MARK858]

Note the triple quotation marks """ to insert a quotation mark into a string

It doesn't need triple quotation marks if you don't concatenate the formula only doubles, see post #2

 

[gallen]

It doesn't need triple quotation marks if you don't concatenate the formula only doubles, see post #2

Didn't realise that. I just tested mine and it worked. Yours is much more succinct.

 

[MARK858]

@jhonty4, sorry didn't put in the wildcards

Range("G7:G" & Range("E" & Rows.Count).End(xlUp).Row).Formula = _
        "=""*""&MID(B7,SEARCH(""("",B7)+1,SEARCH("")"",B7)-SEARCH(""("",B7)-1)&""*"""

or

Range("G7:G" & Range("E" & Rows.Count).End(xlUp).Row).FormulaR1C1 = _
        "=""*""&MID(RC[-5],SEARCH(""("",RC[-5])+1,SEARCH("")"",RC[-5])-SEARCH(""("",RC[-5])-1)&""*"""

Didn't realise that. I just tested mine and it worked. Yours is much more succinct.

I knew it worked, just a small saving on your fingertips

 

[jhonty4]

Thanks Mark it works.

 

[jhonty4]

Thank you Gallen .