CODEWITHSUNDEEP
excelreplaceexcel-formulavba
Chann3rz
Chann3rz

Reputation: 117

Find, Replace and Formula

I want to look for all instances of a word in a particular column (eg. Apple) and replace the contents (eg. Banana) and then on the same row in a different column that already holds a value (eg. 50) insert a formula to calculate 95% of that value (eg. =50*95%).

I have recorded the macro and have achieved this:

Sub Apple50toBanana95()

    ' Find Apple
    ActiveSheet.Range("$A$1:$AV$1600").AutoFilter Field:=1, Criteria1:= _
        "Apple"
    Range("A2").Select
    ' Replace Apple with Banana
    ActiveCell.FormulaR1C1 = "Banana"
    Range("A2").Select
    'Calculate 95% of the value
    ActiveCell.FormulaR1C1 = "=50*95%"
    Range("B2").Select

End Sub

It will only run for the first record in row two. Is there a way to apply it to all instances?

Upvotes: 1

Views: 94

Answers (1)

Krish
Krish

Reputation: 5917

have a look at this help: http://msdn.microsoft.com/en-us/library/office/ff839746(v=office.15).aspx

With Worksheets(1).Range("a1:a500") ' Set your search range
    Set c = .Find("Apple", lookin:=xlValues)
    If Not c Is Nothing Then 
        firstAddress = c.Address 
        Do 
            c.Value = "Banana" ' Replace value
            'add further operations using excel offset feature like c.Offset(1, 1).value = something

            Set c = .FindNext(c) 
        Loop While Not c Is Nothing And c.Address <> firstAddress 
    End If 
End With

Upvotes: 2

Related Questions