[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 262

# Putting formulas in excel VBA

``````    Set BaseWks = Sheets("Summary Data")

n = 2

For n = BaseWks.UsedRange.Row To lastrow
BaseWks.Cells(n, "B").Formula = "= IF(VLOOKUP(C2,nxtRanges,2,FALSE)<>"",(F2/(VLOOKUP(C2,nxtRanges,2,FALSE))),"No Data")"
Next n
``````

Hi,

I have the above formula which is giving me error. I am fairly new to encorporating formulas into VBA. Any help is much aprepeciated!

I m getting error on this part called "Syntax Error"

``````))),"No Data")"
``````
0
Shanan212
• 5
• 2
1 Solution

Excel VBA DeveloperCommented:
You have to use two pairs of double quotes for each single quote needed in the formula:

``````BaseWks.Cells(n, "B").Formula = "=IF(VLOOKUP(C2,nxtRanges,2,FALSE)<>"""",(F2/(VLOOKUP(C2,nxtRanges,2,FALSE))),""No Data"")"
``````
0

Excel VBA DeveloperCommented:
Let me properly phrase:

When inserting a formula from VBA, you need a pair of double-quotes for each individual double-quote used in the formula.

0

Author Commented:
``````        BaseWks.Cells(n, "E").Formula = "=IF(VLOOKUP(Range("C" & n).Value,nxtRanges,2,FALSE)<>"""",(Range("F" & n).Value/(VLOOKUP(Range("C" & n).Value,nxtRanges,2,FALSE))),""No Data"")"
``````

Still a syntax error on the whole line :/
0

Excel VBA DeveloperCommented:
You changed the formula.  The original used VLOOKUP(C2... without a concatenation and quotes.  and you introduced the Range VBA function which is not allowed in Excel.

0

Excel VBA DeveloperCommented:
I see that you're trying to have the row value change with the value of *n*.  The formula now has to be split up into smaller string sections with the n-value concatenated inside:

``````BaseWks.Cells(n,"E").Formula = "=IF(VLOOKUP(C" & n & ",nxtRanges,2,FALSE)<>"""",(F" & n & "/(VLOOKUP(C" & n & ",nxtRanges,2,FALSE))),""No Data"")"
``````
0

Author Commented:
Ow then how do  I keep the formula dynamic throughout the for loop?
0

Excel VBA DeveloperCommented:
My last example should do just that (i.e., make the formula change through the for/next loop).
0

## Featured Post

• 5
• 2
Tackle projects and never again get stuck behind a technical roadblock.