FillDown to variable rows

Question

I have VBA code that has this formula in cell H3

Range("H3").Select

    Selection.FormulaArray = _

        "=IF(RC[-7]=R[-1]C[-7],"""",SUMPRODUCT(1*(FREQUENCY(IF(R2C3:RC3=RC3,MATCH(R2C1:RC1,R2C1:RC1,0)),ROW(R2C2:RC2)-ROW(R1C2))>0)))"

    'Range("H3").Resize(1300).FillDown

My problem is that the size changes each time So .Resize(1300).FillDown needs to be manually checked and changed.

Can someone give me a few lines of code to populate the next cell (H4 to the last row with data in Column G) that will adjust each time it's run?  Keep in mind that the formula can return empty cells.  So I only need the formula to copy as is and beign relative to the cells.

Thanks,

Danielle

Answer 1

Remember that if a cell has even an empty string in it ( "" ), it is not empty.  Maybe there are formulas in column A (that's where it's examining cells, correct?) that return an empty string?  Just to make one up, maybe something like:

=If(MOD(ROW(),2)=0,"",1)

Although I'll admit that "" should evaluate numerically to 0 and not to 1, if memory serves me correctly and you were examining it as a number (which you really aren't).

Keep in mind also, that your code above is going to work with the currently active worksheet - so if you're wanting all this to take place on the [MasterLabel] sheet, you better make sure it is the currently active sheet or explicitly force it:

Set cell = Worksheets("MasterLabel").Range("A2")

at the start should do that for you.

Using Worksheets(1).Range("A2") may not be safe - someone comes along and inserts a new sheet at the start of the workbook, or moves some other one to the leftmost position, the Worksheets(1) wouldn't be pointing to the right sheet anymore.

You might set a breakpoint in the code at the Do While line and single step through the loop for a while and observe what's happening on the sheet itself, and perhaps check using the Immediate window every once in a while to see what address 'cell' is and what address things like cell.Offset(cell.Value,0) evaluate to and make sure they're correct and as expected.

Answer 2

Running more tests on the script, the 99775 represents somehow, the total number of records the script is working with.  I can't figure out how, I am ususally working with 1500 - 2000 rows.

Any ideas?

I have a formula that takes A2:G2 and pastes formulas(not Array formulas) then populates each row down to the last row that matches the data on Worksheet 1( named- MasterLabel)

Dim cell As Range

Set cell = Range("A2")

Do While Not IsEmpty(cell)

    If cell > 1 Then

        Range(cell.Offset(1, 0), cell.Offset(cell.Value - 1, 0)).EntireRow.Insert

        Range(cell, cell.Offset(cell.Value - 1, 1)).EntireRow.FillDown

    End If

    Set cell = cell.Offset(cell.Value, 0)

    Loop

After more debugging, I found that it fills rows through A100007, which it should not.  I think the problem is with this formula.  Can you help?

Answer 3

Did you try my code example?  If you did, does it suffer from the same problems that you reported with Gord Dibben's recommendation?  While I believe they are, in effect, achieving the same goal it may be that internally somehow Excel is handling the straight-forward .FillDown differently than it is handling the .Resize().FillDown process when dealing with an array formula?

Answer 4

This is better, but sometimes the values wind up being greater than 1 or blank.  If I manually copy the formula the values are always 1 or blank.  I am going to run some tests to see if it matters, but why is this happening?

Answer 5

ok, I fgured out that the variable lRow needed to be defined, but the formula is not pasting, I get a value of 99775 in all the rows from H3 to the end.  Do you know why?

I cannot see your data but what do you get when you run this?

Sub test()

    Dim lrow   As Long

    Range("H3").Select

    Selection.FormulaArray = _

    "=IF(RC[-7]=R[-1]C[-7],"""",SUMPRODUCT(1*(FREQUENCY(IF(R2C3:RC3=RC3," _

    & "MATCH(R2C1:RC1,R2C1:RC1,0)),ROW(R2C2:RC2)-ROW(R1C2))>0)))"

    With ActiveSheet

        lrow = Range("A" & Rows.Count).End(xlUp).Row

        Range("H3:H" & lrow).FillDown

    End With

End Sub

Gord