asked on

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

Open in new window

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")"

Open in new window

ASKER CERTIFIED SOLUTION

Glenn Ray

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Glenn Ray

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.

Shanan212

ASKER

        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"")"

Open in new window

Still a syntax error on the whole line :/

Glenn Ray

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.

Glenn Ray

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"")"

Open in new window

Shanan212

ASKER

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

Glenn Ray

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