CODEWITHSUNDEEP
vbaexcel
val
val

Reputation: 5

VBA: Insert If formula and copy formula down entire row

I am trying to insert a formula in a new column (New clicks). My data contains 4 columns - Site, Impressions, Clicks and New Clicks. The formula I want to insert is:

=IF(Site = "Facebook.com", 0,IF(Site = "Google Search", 0, Clicks))

I want to change the clicks number to 0 if the site name is Facebook.com or Google Search, and the formula should apply to all rows in the excel sheet.

Below is my code but I keep getting application/object-defined error when I run it.

Dim lastr1 As Long
Dim strFormulas1 As Variant
Dim b As Variant

lastr1 = Worksheets("Formula").Range("A:A").Find("*", SearchDirection:=xlPrevious).Row

For b = 7 To lastr1
    wb.Sheets("Formula").Range("D" & b).Formula = "=IF(A " & b & ") " = " ""Facebook.com"",""0"",IF(A" & b & ") " = " ""Google Search"",""0""))"
Next b

Upvotes: 0

Views: 2223

Answers (1)

VBA Pete
VBA Pete

Reputation: 2666

You can actually skip a lot of quotation marks and the b-variant by setting the formula equal to the range:

Sub FormulaRng()

Dim lastr1 As Long

lastr1 = Worksheets("Formula").Range("A:A").Find("*", SearchDirection:=xlPrevious).Row

Worksheets("Formula").Range("D7:D" & lastr1).Formula = "=IF(A7=""Facebook.com"",0,IF(A7=""Google Search"",0,C7))"

End Sub

The fields will be automatically updated in the same way as if you would use the Auto fill function.

Upvotes: 1

Related Questions