Excel VBA, finding the row count of a range and applying it to the end of another range

Question

I'm working through a VBA project in Excel (2010 but running in xml, not xmls) and I'm having some troubles. In short, I'm running a =IF((OR(NOT(ISERR(SEARCH....etc in a cell to the very far right of the sheet. Then, I need to xlfilldown, but I need the range to be dynamic. The rows that the IF statement is searching varies from tab to tab, and there is data below it (separated with 1-2 blank rows!) which may contain the same strings i'm searching for, but would provide the wrong data.

So, is it possible to track the row number of the endpoint of the column in the data being IF((OR(NOT(ISERR(SEARCH and then apply that column number as the endpoint of another range, so that the xlfilldown only goes as far as the table? And if so, how would this be done?

Thanks all!!

Range("IF2").Select ActiveCell.Formula = _ "=IF((OR(NOT(ISERR(SEARCH(""perf"",A24))),NOT(ISERR(SEARCH(""profi"",A24))),NOT(ISERR(SEARCH(""commu"",A24))),NOT(ISERR(SEARCH(""equip"",A24))),NOT(ISERR(SEARCH(""occu"",A24))),NOT(ISERR(SEARCH(""emplo"",A24))),NOT(ISERR(SEARCH(""liqu"",A24))),NOT(ISERR(SEARCH(""withholding"",A24))),NOT(ISERR(SEARCH(""ince"",A24))),NOT(ISERR(SEARCH(""trust"",A24))),NOT(ISERR(SEARCH(""mana"", A24))),NOT(ISERR(SEARCH(""mgmt"", A24))),NOT(ISERR(SEARCH(""incentive"", A24)))))=TRUE, C24, 0)"

I need this to fill-down until the last cell containing anything in A, any ideas?

Questions asking for code must demonstrate a minimal understanding of the problem at hand, including attempted solutions, where they failed, etc. — David Zemens
Where it has failed: I can leverage COUNTA to find the number of rows in a table. However, how could I translate this to being the end # of rows which the filldown in another column would traverse? It is possible to assign that value from COUNTA to a varaible. But, would I be able to put that variable in a Range()? Such that I would have Range(XZ_Variable_)? — Findingthedata

Tim Williams Tim Williams · Accepted Answer · 2013-10-07T22:44:50

Your formula can be reduced to something like

=IF(SUM(IFERROR(SEARCH(words,A24),0))>0,C24, 0)

entered as an array formula (Ctrl+Shift+Enter), where words is a named range with the words to search for. You might find that easier to maintain.

Dim f As Range
With ActiveSheet
    Set f = .Cells(Rows.Count, 1).End(xlUp)
    If f.Row >= 24 Then 
        .Range("B24").FormulaArray = _
                "=IF(SUM(IFERROR(SEARCH(words,A24),0))>0,C24, 0)"
        .Range("B24:B" & f.Row).FillDown
    End If
End With

Excel VBA, finding the row count of a range and applying it to the end of another range

1 Answers