Use varilable in vba excel formula

[bishweshwar]

Sub find_string()

Dim i As Integer



i = 150

Cells(42, 6).Formula = "=IF(AND((LEFT(E150,(FIND(""-"",E150)-1)) = ""abc ""),(ISNUMBER(SEARCH(TRIM(RIGHT(E42,(LEN(E42)-FIND(""-"",E42)))), E150)))),TRUE,FALSE)"

End Sub


The above code works fine.

But what am looking for is to use a variable i (initialized to 150) and substitute E150 in the above formula.

So instead of E150, I want to use something like E[i]

How can it be done?

[Cosmo]

Cells(42, 6).Formula = "=IF(AND((LEFT(E150,(FIND(""-"",E" & CSTR(i) & ")-1)) = ""abc ""),(ISNUMBER(SEARCH(TRIM(RIGHT(E42,(LEN(E42)-FIND(""-"",E42)))), E150)))),TRUE,FALSE)"