• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

How do include a variable name in an Excel VBA formula ?

Hi Experts,

This is a problem I still haven't managed to get my head round in Excel VBA. I want to multiply a variable (intLatest) by the value in a cell (10th column). However, this formula always returns an error.
Range("L2:L" & Cells(Rows.Count, 1).End(xlUp).Row).FormulaR1C1 = "=IFERROR(SUM(RC11-(RC10*intLatest)),"""")"

Any ideas ?

Thanks in anticipation
1 Solution
You need your variable to be between quotes and ampersands:
" & intLatest & "

Range("L2:L" & Cells(Rows.Count, 1).End(xlUp).Row).FormulaR1C1 = "=IFERROR(SUM(RC11-(RC10*" & intLatest & ")),"""")"

Open in new window

you could, in your formula, remove the need for 'SUM':
Range("L2:L" & Cells(Rows.Count, 1).End(xlUp).Row).FormulaR1C1 = "=IFERROR(RC11-(RC10*" & intlatest & "),"""")"

Open in new window

TocogroupAuthor Commented:
Thanks Steve,
I get it now. Much appreciated.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now