Solved

# Putting formulas in excel VBA

Posted on 2011-10-24
201 Views
``````    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
Question by:Shanan212

LVL 27

Accepted Solution

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

LVL 27

Expert Comment

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

LVL 13

Author Comment

``````        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

LVL 27

Expert Comment

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

LVL 27

Expert Comment

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

LVL 13

Author Comment

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

LVL 27

Expert Comment

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

## Featured Post

I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…