0

visual basic expression with NF

Jet Reports Historic Posts

May 10, 2013 08:16

None

Follow

I have this expression which doesn't work in Visual Basic (excel 2007):

Cells(f, c + 33) = "=NL(""Primero"",""Registro"",""=(NF(;""Total Ofertado"")-NF(;""Coste directo""))/NF(;""Total Ofertado"")"",""Nº registro"", """ & estudio & """, ""company="",filtroempresa)"

I know the problem is in this side of the expression, related with the "" correct position, but I can't put them correctly.

""=(NF(;""Total Ofertado"")-NF(;""Coste directo""))/NF(;""Total Ofertado"")""



any help?

• Facebook
• Twitter
• LinkedIn

4 comments

• 
  Jet Reports Historic Posts May 13, 2013 15:12 Official comment

  So did you try unquoting the named range? Also, whether you use commas or semicolons depends on whether you're setting Formula or FormulaLocal. Setting FormulaLocal would allow you to write the formula just as you do normally. So in this case you might do this:
  
  Cells(f, c + 33).Formula = "=NL(""Primero"",""Registro"",""=(NF(;""""Total Ofertado"""")-NF(;""""Coste directo""""))/NF(;""""Total Ofertado"""")"",""Nº registro"", """ & estudio & """, ""company="","""&filtroempresa&""")"
  
  Or you could do this:
  
  Cells(f, c + 33).FormulaLocal = "=NL(""Primero"";""Registro"";""=(NF(;""""Total Ofertado"""")-NF(;""""Coste directo""""))/NF(;""""Total Ofertado"""")"";""Nº registro"";"""&estudio &""";""company="";"""&filtroempresa&""")"
  
  Does this help at all?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts May 10, 2013 12:31

  Hi,
  
  I'm not sure why you'd want to torture yourself by writing Jet formulas in VBA, but here you go:
  
  Cells(f, c + 33) = "=NL(""Primero"",""Registro"",""=(NF(;""""Total Ofertado"""")-NF(;""""Coste directo""""))/NF(;""""Total Ofertado"""")"",""Nº registro"", """ & estudio & """, ""company="",filtroempresa)"
  My only other question would be about your company= filter. Is Filtroempresa the actual name of the company or a named range? If it's the name of the company, then you need another set of "" around it. If it's a named range, then you probably need to do this """&filtroempresa&""". Does that help?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts May 13, 2013 07:55

  thank you really!! it works fine , no errors, but the result is #VALUE and can't find the reason (running it as normal jet formula works fine).
  filtroempresa is a named range and I already have changed =(NF(; by =(NF(, which I had forgoten to correct.
  
  any idea?maybe another "" correction?

• 0
  
  Jet Reports Historic Posts May 13, 2013 15:37

  you are the best!!
  
  using the ".FormulaLocal" version it works perfectly! (filtroempresa unquoted).
  I don't know why but with ".Formula" doesn't work for me…
  
  anyway, you gave me the solution, thanks a lot for your time
  cheers!!!