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

Yes, it is an array formula, and I have no idea what the value of 99775 is.  Changing the formatting of the fields with that value does not affect it.

Answer 2

Well, first question of mine is does 99775 have any meaning at all in this context?  That is, could it be a valid result for at least one row?

Have you looked at the created formula to verify that it appears valid?  OH, and does it appear to be an array formula (with {} braces at the start and end of it)?

 Or maybe try it this way, and as Gord Dibben said, assumes that column A has entries as far down the sheet as you need to go:

Sub Test2()

  Dim lRow As Long

  Dim newSize 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)))"

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

  newSize = lRow - 2 ' could have written lRow-3+1, but -2 takes care of the +1

  Range("H3").Resize(newSize).FillDown

End Sub

Answer 3

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?

Answer 4

Adding this to my pre-existing code give me a compile error- Invalid or unqualified reference with .Range highlighted.

Do you know what this means?

Answer 5

You must have one column that you can use to find the last used row.

Assuming that would be Column A

With ActiveSheet

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

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

 End With

Gord