CODEWITHSUNDEEP
vbaexcel
Sherbetdab
Sherbetdab

Reputation: 127

VBA add Formula to cells containing specific text

I'm trying to add a lookup formula to cells in a range where the word YES appears and leave the text in all other cells in the range as they are. My code is.

Sub AddFormula()

Dim SrchRng As Range, cel As Range

Set SrchRng = Range("B1:B20")

For Each cel In SrchRng
    If InStr(1, cel.Value, "Yes") > 0 Then
        cel.Value = "=VLOOKUP(A1,H:I,2,0)"

    End If
Next cel

End Sub

Unfortunately the cell reference 'A1' does not change as the formula is entered. Can anyone help please?

Upvotes: 0

Views: 97

Answers (2)

LM358
LM358

Reputation: 141

Here is simple solution to get the cell left to cel instead of always A1:

Sub AddFormula()

Dim SrchRng As Range, cel As Range

Set SrchRng = Range("B1:B20")

For Each cel In SrchRng
    If InStr(1, cel.Value, "Yes") > 0 Then
        cel.Value = "=VLOOKUP(A" & cel.Row & ",H:I,2,0)"
    End If
Next cel

End Sub

Upvotes: 1

Drimacus
Drimacus

Reputation: 11

The code doesnt look like it's supposed to change values in 'A1'. The loop is over cells in B1:B20, so only cells in B1:B20 can change. Maybe you are not getting any changes expected because your if condition is never true? I would suggest using the Immediate Window to check if the if condition is ever true with "Debug.print. Also better to use R1C1 references like this:

Sub AddFormula()

    Dim SrchRng As Range, cel As Range

    Set SrchRng = Range("B1:B20")

    For Each cel In SrchRng
        If InStr(1, cel.Value, "Yes") > 0 Then
            Debug.Print "Was true"
            cel.FormulaR1C1 = "=VLOOKUP(RC[-1],C[6]:C[7],2,0)"
        End If
    Next cel

End Sub

Upvotes: 1

Related Questions