Okay so I am trying to map data and on the following line wsTarget.Cells(lastRowTarget + 1, 5).value = "=+Financials!B1" what I want is that after mapping my column should have this Formula "=+Financials!B1" instead what I get is value of that column, I have tried multiple approaches including Copy Paste the column and wsTarget.Cells(lastRowTarget + 1, 5).Formula = "=+Financials!B1" but nothing has worked so far as whatever I try it fetches the value instead of adding the formula. The value in column Financials B1 will change after the mapping. Any Ideas how I can fix this issue?

    wsTarget.Cells(lastRowTarget + 1, 2).value = "project_type"
    wsTarget.Cells(lastRowTarget + 1, 4).value = "year_in_files"
    wsTarget.Cells(lastRowTarget + 1, 5).value = "=+Financials!B1"
    wsTarget.Cells(lastRowTarget + 1, 7).value = companyName
    wsTarget.Cells(lastRowTarget + 1, 8).value = projectName ```

Okay so I am trying to map data and on the following line wsTarget.Cells(lastRowTarget + 1, 5).value = "=+Financials!B1" what I want is that after mapping my column should have this Formula "=+Financials!B1" instead what I get is value of that column, I have tried multiple approaches including Copy Paste the column and wsTarget.Cells(lastRowTarget + 1, 5).Formula = "=+Financials!B1" but nothing has worked so far as whatever I try it fetches the value instead of adding the formula. The value in column Financials B1 will change after the mapping. Any Ideas how I can fix this issue?

    wsTarget.Cells(lastRowTarget + 1, 2).value = "project_type"
    wsTarget.Cells(lastRowTarget + 1, 4).value = "year_in_files"
    wsTarget.Cells(lastRowTarget + 1, 5).value = "=+Financials!B1"
    wsTarget.Cells(lastRowTarget + 1, 7).value = companyName
    wsTarget.Cells(lastRowTarget + 1, 8).value = projectName ```

• excel
• vba

• Unable to repro. This writes a formula that references Financials!B1. Do you have any other code modifying the sheet? – BigBen Commented Nov 21, 2024 at 18:31
• If your sheet is named +Financials, use ... .Formula = "='+Financials'!B1". – VBasic2008 Commented Nov 23, 2024 at 15:52

2 Answers 2

Rather than repeating lastRowTarget + 1 in the code consider using With

With wsTarget.Rows(lastRowTarget + 1)
    .Columns("B") = "project_type"
    .Columns("D") = "year_in_files"
    .Columns("E").Formula = "=Financials!B1"
    .Columns("G") = companyName
    .Columns("H") = projectName
End With

What I think you are asking for, is writing a formula to a cell. The issue arises because the .Value property assigns the result of the formula (its value) rather than the formula itself. To insert the formula so that it remains dynamic and updates with changes to the referenced cell, you need to use the .Formula property explicitly.

wsTarget.Cells(lastRowTarget + 1, 2).Value = "project_type"
wsTarget.Cells(lastRowTarget + 1, 4).Value = "year_in_files"
wsTarget.Cells(lastRowTarget + 1, 5).Formula = "=Financials!B1"

By the way, you do not need to use the plus sign after the equal sign in a formula.