CODEWITHSUNDEEP
excelvba
Stacy Ryutt
Stacy Ryutt

Reputation: 49

Filling a n x 3 range with formulas that contain iteratable reference to a list in Excel VBA

I want to fill a range of n rows and 3 columns with 3 formulas, one in each column.

The first column should contain a date range.

I fill it this way:


Sub FillDate()

    Dim startDate As Date
    Dim endDate As Date
    Dim row As Long

    startDate = "01.08.2019"
    endDate = "07.08.2019"
    row = 2
    Do Until startDate = endDate + 1
        Range("A" & row).Value = startDate
        startDate = startDate + 1
        row = row + 1
    Loop  
End Sub

This gives me the A column with dates from 01.08.2019 to 07.08.2019 starting from the second row.

In columns B and C I'd like to specify tow formulas.

Formula for column B should look as follows:

=COUNTIF('Sheet (i)'!G$2:G$5000, $A2)

where i is an iterator so that formula in B2 should look as

=COUNTIF('Sheet (1)'!G$2:G$5000, $A2)

and formula in B3 should look as

=COUNTIF('Sheet (2)'!G$2:G$5000, $A3)

and so forth.

This formula looks up for the value in cell A2 and calculates how many times this value is found in the range G$2:G$5000 on the sheet named Sheet (1).

Formula for column C should look as follows:

=ROUND(MEDIAN($B$2:$B$n), 0)

where n is the number of rows in column A on the current sheet.

Like

=ROUND(MEDIAN($B$2:$B$8), 0)

I've come with the following, ehm, solution:

Sub Fill()

Dim strFormulas(1 To 2) As Variant
Dim wb As Workbook: Set wb = ActiveWorkbook
Dim i As Integer

'Application.ScreenUpdating = True

With ThisWorkbook.Sheets("Test")

     strFormulas(1) = "=COUNTIF('Sheet (1)'!G$2:G$5000, $A2)"
     strFormulas(2) = "=ROUND(MEDIAN($B$2:$B$8), 0)"

     .Range("C2:D2").Formula = strFormulas
     .Range("C2:D8").FillDown
End With
End Sub

However, this only fills the column C with proper formula.

Question: how do I fill the column B so that B2 cell contains =COUNTIF('Sheet (1)'!G$2:G$5000, $A2), B3 cell contains =COUNTIF('Sheet (2)'!G$2:G$5000, $A2) and so on up to =COUNTIF('Sheet (8)'!G$2:G$5000, $A2)?

Thank you in advance.

Upvotes: 0

Views: 56

Answers (1)

simple-solution
simple-solution

Reputation: 1139

Sub Fill()

Dim strFormula_template As String
Dim strFormulas(1 To 2) As Variant
Dim wb As Workbook: Set wb = ActiveWorkbook
Dim iCt As Integer

'Application.ScreenUpdating = True

With ThisWorkbook.Sheets("Test")

    strFormula_template = "=COUNTIF('Sheet (count)'!G$2:G$5000, $Axx)"
    strFormulas(2) = "=ROUND(MEDIAN($C$2:$C$8), 0)"

    For iCt = 1 To 7
        strFormulas(1) = Replace(strFormula_template, "count", iCt)
        strFormulas(1) = Replace(strFormulas(1), "xx", iCt + 1)
        .Range("C1:D1").Offset(iCt, 0).Formula = strFormulas
    Next

End With
End Sub

Upvotes: 1

Related Questions