excel - 在具有不同偏移量的公式中增加单元格引用 - Excel 宏

Question

我正在尝试创建一个涉及在两个工作簿(例如 wb1 和 wb2)之间复制数据的 excel 宏。
在 wb2 中，我想将某个公式分配给特定列的一系列单元格。所以公式中引用的 wb1 的单元格必须递增。

F1(wb2) = COUNTIF(range in wb1,contidion)*A1(wb1)*B1(wb1)
F5(wb2) = COUNTIF(range in wb1,condition)*A2(wb1)*B2(wb1)
.
.
.
F50(wb2)= COUNTIF(range in wb1,condition)*A10(wb1)*B10(wb1)

如何使用 excel Macros 实现这一目标？

Answer 1

这是组成字符串以构建每个单元格的公式的简单事项。下面的代码将在 wb2 中构建这些公式:

F5: =COUNTIF([Book1.xlsm]Sheet1!D1:D25,">5")*[Book1.xlsm]Sheet1!A1*[Book1.xlsm]Sheet1!B1
F10: =COUNTIF([Book1.xlsm]Sheet1!D1:D25,">5")*[Book1.xlsm]Sheet1!A2*[Book1.xlsm]Sheet1!B2
F15: =COUNTIF([Book1.xlsm]Sheet1!D1:D25,">5")*[Book1.xlsm]Sheet1!A3*[Book1.xlsm]Sheet1!B3
F20: =COUNTIF([Book1.xlsm]Sheet1!D1:D25,">5")*[Book1.xlsm]Sheet1!A4*[Book1.xlsm]Sheet1!B4
F25: =COUNTIF([Book1.xlsm]Sheet1!D1:D25,">5")*[Book1.xlsm]Sheet1!A5*[Book1.xlsm]Sheet1!B5
F30: =COUNTIF([Book1.xlsm]Sheet1!D1:D25,">5")*[Book1.xlsm]Sheet1!A6*[Book1.xlsm]Sheet1!B6
F35: =COUNTIF([Book1.xlsm]Sheet1!D1:D25,">5")*[Book1.xlsm]Sheet1!A7*[Book1.xlsm]Sheet1!B7
F40: =COUNTIF([Book1.xlsm]Sheet1!D1:D25,">5")*[Book1.xlsm]Sheet1!A8*[Book1.xlsm]Sheet1!B8
F45: =COUNTIF([Book1.xlsm]Sheet1!D1:D25,">5")*[Book1.xlsm]Sheet1!A9*[Book1.xlsm]Sheet1!B9
F50: =COUNTIF([Book1.xlsm]Sheet1!D1:D25,">5")*[Book1.xlsm]Sheet1!A10*[Book1.xlsm]Sheet1!B10

这是代码:

Sub copy_cells()
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim row As Integer
    
    Set wb1 = Workbooks("book1.xlsm")
    Set wb2 = Workbooks("book2.xlsm")
 
    For row = 1 To 10
        wb2.Worksheets(1).Cells(row * 5, "F").Formula = "=COUNTIF([Book1.xlsm]Sheet1!D1:D25,"">5"")*[Book1.xlsm]Sheet1!A" & row & "*[Book1.xlsm]Sheet1!B" & row
    Next
 
End Sub