VBA:如何将formulaS拖到最后使用的行？

Question

日安，请帮帮我。场景是这样的，在标题之后，我有7个不同的公式分配给7个不同的顶部行。我想要实现的是同时将这个公式拖到最后使用的行。我成功地写了一个代码，但是它是一个静态代码，我想用一种动态的方式去做，因为每个月的数据量是不同的，所以我的静态代码不可靠。

下面是我写的代码：

格式边框

 ActiveSheet.Range("BK1", "BQ22").Select

borderMeFn

                Dim strFormulas_OR1_ASR_DATA_DETAILS(1 To 7) As Variant
                    strFormulas_OR1_ASR_DATA_DETAILS(1) = "=COUNTIF(LSR_WISOR_USERS_" & month & ".xlsx!$C:$C,J2)" 'WISOR_COUNT - counts the PON_VER in LSR_WISOR_USER file.
                    strFormulas_OR1_ASR_DATA_DETAILS(2) = "=IFERROR(VLOOKUP(J2,LSR_WISOR_USERS_" & month & ".xlsx!$C:$E,3,FALSE),"""")"
                    strFormulas_OR1_ASR_DATA_DETAILS(3) = "=IFERROR(VLOOKUP(H2,CPXLIST_" & month & ".xlsx!$A:$B,2,FALSE),"""")"
                    strFormulas_OR1_ASR_DATA_DETAILS(4) = "=IFERROR(VLOOKUP(H2, DDVRFY_" & month & ".xlsx!$A:$B,2,FALSE),"""")"
                    strFormulas_OR1_ASR_DATA_DETAILS(5) = "=IFERROR(VLOOKUP(H2,HTG_" & month & ".xlsx!$A:$D,4,FALSE),"""")"
                    strFormulas_OR1_ASR_DATA_DETAILS(6) = "=IFERROR(VLOOKUP(H2,RPON_" & month & ".xlsx!$A:$B,2,FALSE),"""")"
                    strFormulas_OR1_ASR_DATA_DETAILS(7) = "=IFERROR(VLOOKUP(H2,PROV_PLAN_" & month & ".xlsx!$A:$F,6,FALSE),"""")"
                'apply formulas to designated cells
                With ActiveWorkbook.Sheets("Sheet1")
                    .Range("BK2:BQ2").formula = strFormulas_OR1_ASR_DATA_DETAILS
                End With
                Worksheets("Sheet1").Range("BK2:BQ22").FillDown
                    'Range("BK2:BQ" & LastRow).FillDown

提前谢谢你。

Answer 1

试试下面的代码，代码注释中的说明：

Dim LastRow As Long, LastCell As Range

'apply formulas to designated cells
With ThisWorkbook.Worksheets("Sheet1")
    .Range("BK2:BQ2").Formula = strFormulas_OR1_ASR_DATA_DETAILS

    ' use Find function to get last row 
    Set LastCell = .Cells.Find(What:="*", After:=.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, _
                        SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
    If Not LastCell Is Nothing Then
        LastRow = LastCell.Row
    Else
        MsgBox "Error!", vbCritical
    End If

    .Range("BK2:BQ" & LastRow).FillDown
End With