Errors with AutoFilling Equations VBA

Question

Sub Reflector()

Dim i As Integer
Dim endRow As Integer
endRow = Cells(Rows.Count, "B").End(xlUp).Row

Range("G2").Select

For i = 2 To 40

Range("G" & i).Select

    If Range("H" & i).Value = "1" Or Range("H" & i).Value = "-1" Then

    ActiveCell.Formula = "=ROW(A1)"

    ActiveCell.AutoFill Destination:=Range(ActiveCell.Address & ":G" & endRow)

    End If

Next i

End Sub

What I'd like is for the Macro to check values in column H and keep a tally of times it has NOT -1 or 1 then write that tally in column G. If it hits a value with 1 or -1 in column G, I'd like it to restart the count there.

The process is that I will check Column H and autofill to last row of data and "bounce back up" to check the next value and then autofill again if it meets a 1 or -1.

Here is the error I get:

This logic will be used for a more complex formula later on. Row(A1) is a bit of a holding formula. — AltoidsBenefitsH, Feb 17 '16 at 17:51
_This logic will be used for a more complex formula later on._ The idea of autofilling whole range with complex formula multiple times in a loop doesn't look like a good approach. For me it's XY problem. — BrakNicku, Feb 17 '16 at 18:16

score 1 · Accepted Answer · edited May 23 '17 at 10:30

1

Avoid using Select in and ActiveCell (unless absolutely necessary).

Try this code:

Sub Reflector()

Dim ws as Worksheet
Set ws = WOrksheets("Sheet1") 'change as needed

Dim i As Integer
Dim endRow As Integer
endRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

For i = 2 To 40

    If ws.Range("H" & i).Value = "1" Or ws.Range("H" & i).Value = "-1" Then

        With ws.Range("G" & i)
            .Formula = "=ROW(A1)"
            .AutoFill Destination:=ws.Range(ws.Range("G"&i),ws.Range("G" & endRow))
        End With

    End If

Next i

End Sub

UPDATE

To @ScottCraner's point this bit of code will also work and is easier to read / maintain.

Dim ws As Worksheet
Set ws = Worksheets("Sheet1") 'change as needed

Dim endRow As Long
endRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

With ws.Range("G2:G" & endRow)
    .FormulaR1C1 = "=IF(OR(RC[1]=1,RC[1]=-1),1,R[-1]C+1)"
End With

Screenshot of tested code:

edited May 23 '17 at 10:30

Community

1
1

answered Feb 17 '16 at 17:39

Scott Holtzman

27,099
5
37
72

Hi Scott, thanks for the reply. Still giving me #REF! for those cells minus the last one. – AltoidsBenefitsH Feb 17 '16 at 17:50
see my results. that is what I got when I ran the code in my answer as is. – Scott Holtzman Feb 17 '16 at 17:55
I tried it on a completely new spreadsheet with that data in columns G and H but I got a different result. Your code seems correct but I don't understand why I am getting all these #REF! results. Added updated picture – AltoidsBenefitsH Feb 17 '16 at 18:03
@AltoidsBenefitsH - see my 2nd piece of code in my answer. it's much simpler and will work as well. – Scott Holtzman Feb 17 '16 at 18:07
I am still new to VBA programming, is there problems with code across versions? The new code and the original code seems to not be behaving well.. the new code goes up to the first row and starts running. – AltoidsBenefitsH Feb 17 '16 at 18:12
@AltoidsBenefitsH - the problem is `endRow` you are evaluating it based on the last data point in column B. If you are using a testing sheet and column B is blank `endrow` will equal 1. Adjust for this and it will work. For example, use column H instead. – Scott Holtzman Feb 17 '16 at 18:14
That fixed it! Thank-you so much for your time sir – AltoidsBenefitsH Feb 17 '16 at 18:16

Errors with AutoFilling Equations VBA

1 Answers1