Selection Formula=FormulaLocal

[alfredo sanchez]

Hi!
Hope some of you guys can help me with this one:
I'm modifying references in formulas via vba, but when the macro writes back the cells' value, I get #NAME? error. So I'm trying to write back the formula as FormulaLocal in order to my XL version be able to work with it.
This are the lines of code I'm using

  For i = 1 To c_formls
    If Selection.Cells(i).HasFormula Then
          Selection.Cells(i).Formula = Application.ConvertFormula _
          (Formula:=Selection.Cells(i).Formula, FromReferenceStyle:=xlA1, _
          ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)
    End If
    Next i

c_formls has the number of cells in ActiveSheet that have formula in them

Can you help me to put the FormulaLocal entry in the right place?

Thank you in advance.

 

[Colo]

Hi,

Why don't you try to use XL-Dennis's FF-Translator?
http://www.interq.or.jp/sun/puremis/colo/HtmlMaker.htm

 

[alfredo sanchez]

thank you COLO for the link, great site and utilities, but - there's always a but -, how can I provide my macro with that function?.
I'll explain: I'm using XL 2k2XP spanish version with VBE to edit my code, the problem is that VBA is NOT spanish version, i.e. if you want excel to OFFSET(1,0), you have to write OFFSET and not DESREF which is the spanish command. The same apply for built-in Functions, so when I try to change relative to absolute references via VBA, it returns the english version that the spreadsheet doesn't recognize.
Hope you can help me.

This is the line of code I'm using:

Selection.Cells(i).Formula = Application.ConvertFormula _
          (Formula:=Selection.Cells(i).Formula, FromReferenceStyle:=xlA1, _
          ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)

 

[Juan Pablo González]

What does this return ?

MsgBox Application.ConvertFormula _ 
          (Formula:=Selection.Cells(i).Formula, FromReferenceStyle:=xlA1, _ 
          ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)

 

[alfredo sanchez]

Hi Juan Pablo.

I'm using cell B24 which have formula: =SUMA(B22:R22), the task I'm trying is to get =SUMA($B$22:$R$22), but applying the line of code I get:
SUM(B22:R22) which in-screen shows #NOMBRE?

 

[Juan Pablo González]

But the MsgBox says SUM or SUMA ?

 

[Juan Pablo González]

Having in A3 this

=SUMA(A1:A2)

this worked ok for me:


ActiveCell.Formula = Application.ConvertFormula(ActiveCell.Formula,xlA1,xlA1,1)

 

[alfredo sanchez]

there is no messagebox, code stops with "No coinciden los tipos"




Podremos pasar este topico a la seccion de español, para explicar mejor?

 

[Juan Pablo González]

Listo, y si se cambia

Selection.Cells(i)

por

Selection(i).Formula

?

 

[alfredo sanchez]

ok, last line worked fine, thanks a lot