0

Again, I need some help. I have declared the last Column of a worksheet and want to use the value of it create a formula that is pasted into the worksheet. The goal of it is, that it puts in a formula that sums up the 2 cells above it and that i automatically updates in the spreadsheet if I enter different numbers above manually.

wsDATA.Cells(16, wsDATALcol + 1).Formula = "= cells(14, wsDATAlcol.value+1) + cells(15, wsDATALcol + 1)"

I have tried to use different methods, including =SUM(Range(...) and using .FormulaR1C1, but I am stuck.

A quick hint would be highly appreciated.

Improve this question

2 Answers 2

Reset to default
1

I see three probable causes:

  1. You are referencing variables inside a string.
  2. You have used wsDATALcol with both an uppercase L and lowercase l.
  3. wsDATAlcol.value would be referring to a range but I'm guessing it should be the same as wsDATALcol.

To fix #1, concatenate your values:

wsDATA.Cells(16, wsDATALcol + 1).Formula = "= cells(14, " & wsDATALcol.Value + 1 & ") + cells(15, " & wsDATALcol + 1 & ")"
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks for you answers @Portland! From what I have learned so far your second point is not valid. VBA should change the text that you type in, if it is entered correctly. So in my solution I posted below, I could enter wsdatalcol and it would automatically correct it to wsDATALcol.
That's true but you posted code with different cases and since one was used a .value after it, VBA wasn't automatically updating it. Give it a try and I think you'll see what I see.
0

I solved it.

wsDATA.Cells(16, wsDATALcol + 1).FormulaLocal = "=(" & Cells(14, wsDATALcol + 1) & "+" & Cells(15, wsDATALcol + 1) & ")"
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.