1

I have the following US-English version of the formula pasted in Cell C15 of Sheet TemplateMap as:

'=IF(AND($H11="H",L11>=1,ISNUMBER(L11)),TRUE,IF(AND($H11<>"H",L11>=0,ISNUMBER(L11)),TRUE,FALSE))

Where some columns are absolute and rows relative. This formula is to be applied in VBA code to a the cells of a Sheet Base_Price.

For a user in Poland, the polish formula would be:

'=IF(AND($H11="H";L11>=1;ISNUMBER(L11));TRUE;IF(AND($H11<>"H";L11>=0;ISNUMBER(L11));TRUE;FALSE))

ISSUE:

  • In the formula, the "," commas do not get translated to ";" semicolons for Poland.
  • Also, the .RefersToLocal gives incorrect Column and Row references (unless I make Absolute all the rows and columns in the original formula).
  • Active Sheet name gets added to the formula in .RefersToLocal.

Here is what I tried:

Sub Tester()
    Dim V As Variant
    
    V = FormulaToLocal(ThisWorkbook.Worksheets("TemplateMap").Range("C15"))
    Debug.Print V
End Sub

Function FormulaToLocal(ByRef rCell As Range) As Variant
    Dim x As Variant
    
    ThisWorkbook.Names.Add "myFormula", RefersTo:=rCell.Formula
    FormulaToLocal = ThisWorkbook.Names("myFormula").RefersToLocal
    
    ThisWorkbook.Names("myFormula").Delete
End Function

the output that i am getting if i activate Base_Price sheet:

=IF(AND(Base_Price!$H16="H";Base_Price!U16>=1;ISNUMBER(Base_Price!U16));TRUE;IF(AND(Base_Price!$H16<>"H";Base_Price!U16>=0;ISNUMBER(Base_Price!U16));TRUE;FALSE))

the output that i am getting if i activate TemplateMap sheet:

=IF(AND(TemplateMap!$H25="H";TemplateMap!M25>=1;ISNUMBER(TemplateMap!M25));TRUE;IF(AND(TemplateMap!$H25<>"H";TemplateMap!M25>=0;ISNUMBER(TemplateMap!M25));TRUE;FALSE))

The expected output should be as shown below without activating any sheets or adding sheet name to original formula:

'=IF(AND($H11="H";L11>=1;ISNUMBER(L11));TRUE;IF(AND($H11<>"H";L11>=0;ISNUMBER(L11));TRUE;FALSE))```
sifar
  • 1,086
  • 1
  • 17
  • 43
  • Is there any reason you're not using [`Range.FormulaLocal`](https://learn.microsoft.com/en-us/office/vba/api/excel.range.formulalocal)? – BigBen May 16 '23 at 14:07
  • 1
    @BigBen the english formula is pasted as a text string in C15 starting with "'" e.g. `'=IF(AND($H11="H",L11>=1,ISNUMBER(L11)),TRUE,IF(AND($H11<>"H",L11>=0,ISNUMBER(L11)),TRUE,FALSE))`. So when i try in polish settings, Range.FormulaLocal gives error. therefore, resorted to names technique. – sifar May 16 '23 at 14:15
  • Oh. "This formula is to be applied in VBA code to a the cells of a Sheet Base_Price." How are you writing this formula to a cell with VBA? In other words, just wanted to make sure you're aware of the info [here](https://stackoverflow.com/questions/35724156/different-languages-issue-when-inserting-formula-from-vba). If you have the US-English version of the formula, why not just write that using `.Formula`? – BigBen May 16 '23 at 14:17
  • as a formula in a custom data validation. – sifar May 16 '23 at 14:20
  • One option might be to write the formula (minus the `'`) to a cell, and then read the `.FormulaLocal` back from the same cell? – BigBen May 16 '23 at 14:21
  • tried that. did not work! if i remove the apostrophe from formula, then in polish settings, the cell will trigger an error dialog, stating `there is a problem with this formula`. If you share an example of how you did it, that would help. – sifar May 16 '23 at 14:22
  • 1
    Also, you can simplify your formula to `=AND(ISNUMBER(L11),OR(AND($H11="H",L11>=1),AND($H11<>"H",L11>=0)))`. – BigBen May 16 '23 at 14:27
  • thanks for the updated formula. but my main issue is with regional settings and making the formula translate across regions, so that custom data validation works properly and does not shoot any 1004 error. If you see, for polish region, the "," gets translated to ";" in the formula and the column references and row references also get changed. – sifar May 16 '23 at 14:30
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/253679/discussion-between-sifar-and-bigben). – sifar May 16 '23 at 15:01

0 Answers0