Incrementing cell reference in a formula with varying Offsets - Excel Macro

user_17259466 0
Viewed 50

I am trying to create an excel macro which involves copying data between two workbooks (say wb1 and wb2).

In wb2 I want to assign a certain formula to a series of cells of a specific column. So the cell of wb1 referenced in the formula has to be incremented.

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)

How can I acheieve this using excel Macros ?

excel-formula vba excel
2 Answers

Copying the values from one workbook with another is not too difficult, but I'm unclear why you at indicating that the destination cells (e.g. F5 on wb2) already have values. If you want to copy values to those destination cells, it will replace the data already there. Here is a macro to do the copying. This code assumes that book1 and book2 are already open. The code copies data from the first worksheet in book1 and puts it in the first sheet of book2.

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").Value = wb1.Worksheets(1).Cells(row, "D").Value
    Next
 
End Sub
user_22206650

This is a simple matter composing the string to build the formula for each cell. The code below will build these formulas in 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

Here is the code:

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
user_22206650
Related