CODEWITHSUNDEEP
excelvba
jun
jun

Reputation: 55

Why is my VBA code applying formula to all rows under a column?

I want to apply a formula only to rows that satisfy a condition, but it is somehow applying the formula to all rows.

I have an Excel sheet with a table in it. For column Q, I want to add a formula only if column K's value is NOT 0, and column I's value is 0. But instead of applying it to rows that met the condition, it is applying the formula to all rows under column Q. I added if-statement, and it seems to be working when I run the program in debug mode, and check if the correct rows are going into the if condition.

What is weird is, if I try to put some random string into the rows for "else", it works! But I want nothing in those rows that don't satisfy the condition. 

Dim lRow, i As Integer
lRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To lRow 'Starts from i=2 because i=1 is the headers

    If Range("K" & i).Value <> 0 And Range("I" & i).Value = 0 Then
        Range("Q" & i).FormulaR1C1 = "=RC[-6]"  'This is the formula.
    Else
        Range("Q" & i).Value = "" 'Doesn't work.
        Range("Q" & i).Value = "XXX" 'Works, but it inserts "XXX" to the cells. Ideally, I dont want anything, any formula in the cells.
    End If   
Next i

I expect that the rows that satisfy the condition should get the formula inserted under column Q, but I don't see why my code doesn't work.

Upvotes: 0

Views: 287

Answers (1)

Earl_of_Duke
Earl_of_Duke

Reputation: 358

As GSerg points out in the comments this behaviour is due to Excel autofilling the formula as it is in a table.

You can insert the following into your code which will turn off the autofill:

Application.AutoCorrect.AutoFillFormulasInLists = False

Remember to set it back to True again if it is desired behaviour for you normally (or if you are sharing this macro with others).

Upvotes: 4

Related Questions