Add rows.count to the first cell of a column

Question

I have Table1 which has 159 rows in column A. let's say the n = 159. I would like to use this number in another worksheet vba to determine the target range in Column G.Column G is not part of the table. I am used to coding in python and have a beginner's understanding of vba syntax. Would this be possible? Below is a sample of a code i researched in this site.

Sub FillDown()
    Dim sh As Worksheet
    Dim rn As Range
    Dim strFormulas(1) As Variant
    Set sh = ThisWorkbook.Sheets("Analysis")

    Dim k As Long

    Set rn = sh.UsedRange
    k = rn.Rows.Count + rn.Row - 1


    With ThisWorkbook.Sheets("Analysis")
        strFormulas(1) = "=IFERROR(OR(Sheet1!A1:Z1), "")"

        ActiveSheet.Range("F1").Formula = strFormulas
        ActiveSheet.Range("F1:F").FillDown
    End With

End Sub

In this line: ActiveSheet.Range("F1:F").FillDown

I would like to add the 'n': ActiveSheet.Range("F1 + n").FillDown

But this one doesn't work. I am also open to any resources you can provide for vba syntax specially for complex formulas

Thanks!

`"=IFERROR(OR(Sheet1!A1:Z1), """")"` - you need to double up inner quotes. — BigBen, Jun 05 '20 at 13:35

JvdV · Answer 1 · 2020-06-05T14:18:58.337

Without really being able to test I picked up some mishaps in your code.

You are filling an array with a formula? Just use a String variable if any at all.
UsedRange is a very unreliable way of retrieving your last used row. See this link for some explaination.
You have not thought through how to use your Worksheet variable "rn". You Set it up, but when you actually want to use it in a With statement, you A) don't refer to your variable and B) went back to the evil ActiveSheet.
F1:F Is bad VBA syntax. It's valid Google Sheets syntax which might have thrown you off. You'd need to concatenate the last used row variable in this case.
To create a valid formula you need to double up the quotes in your string for VBA to pick up the "" as a parameter in the used formula.
You don't need to FillDown when VBA will auto-adjust the references in your formula.

See if the following does the trick:

Sub FillDown()

    Dim sh As Worksheet
    Dim rn As Range, k As Long, strFormulas As String

    Set sh = ThisWorkbook.Worksheets("Analysis")
    With sh
        k = .Cells(.Rows.Count, 1).End(xlUp).Row
        strFormulas = "=IFERROR(OR(Sheet1!A1:Z1), """")"
        .Range("F1:F" & k).Formula = strFormulas
    End With

End Sub

Though, the final result makes no sense since your formula looks wonky to begin with. Your next step in the process I'd say =)

Good luck.

Add rows.count to the first cell of a column

1 Answers1