CODEWITHSUNDEEP
vbaexcel
K.EL
K.EL

Reputation: 65

How to use quotes within a formula along with Active cell formula

So I am trying to go for dynamic sheet referencing and this is the code I have so far:

 Application.Range(strNewCell2).Activate
    ActiveCell.Formula = "=SUM(INDIRECT(inputVal& "!H2:H402"))"

Note: inputVal is a variant variable that contains the sheet name

However when I run it, it gives me an unexpected end of statement error.

The other method that I tried is: 

Application.Range(strNewCell2).Activate
    ActiveCell.Formula = "=SUM(INDIRECT(inputVal& ""!H2:H402""))"

I tried this after looking up many things, however that isn't the correct way either as it escapes the double quotes but still keeps one set of them within the formula, which gives me the wrong result.

So what I am trying to do is somehow use indirect within the formula statement without getting an error.

Upvotes: 1

Views: 160

Answers (1)

user4039065
user4039065

Reputation:

Try,

Application.Range(strNewCell2).Activate
ActiveCell.Formula = "=SUM(INDIRECT(""'" & inputVal & "'!H2:H402""))"

However, I'm curious as to why you require INDIRECT at all if you are constructing the worksheet/range reference.

ActiveCell.Formula = "=SUM('" & inputVal & "'!H2:H402)"

Upvotes: 1

Related Questions