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

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

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

0
Shanan212
Asked:
Shanan212
  • 5
  • 2
1 Solution
 
Glenn RayExcel 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"")"

Open in new window

0
 
Glenn RayExcel 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
 
Shanan212Author 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"")"

Open in new window


Still a syntax error on the whole line :/
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Open in new window

0
 
Shanan212Author Commented:
Ow then how do  I keep the formula dynamic throughout the for loop?
0
 
Glenn RayExcel VBA DeveloperCommented:
My last example should do just that (i.e., make the formula change through the for/next loop).
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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