Vlookup in Excel with VB code

[Riun]

Hi,
I need to write VB code in Excel to check in each row of a column for the word "reference". If the word is there, I need to change the code in the same row, but another column, to "Left(G3,1)". (but not G3, it must be column G, but the row must be the same as the row that "reference" is in).

This is my present code, (but obviously wrong):

 Dim a
For Each a In Range("F3:F4000")
If Mid$(a.Formula, 1, 1) <> "=" Then Mid$(a.Formula, 1, 15) = "Left(G18,1)"
Next
 
 
     Dim Counter
     Counter = 0
     i = 3
     While i = 0
     If Sheets("Main").Cells(i, 22) = "Reference" Then toets = False
     Counter = i + 1
     Wend
 

Please assist me if possible
Tnx!