I'd like to prepare an overview tab with some formulas, though not all colleagues use an en-us version of Excel - I currently use de-at languages pack. Therefore I started using .FormulaLocal but it doesn't quite work in my case
In this example I try to prepare a formula that sums up all "light" errors from various tabs. I have two versions
Version 1 (does work with de-at Excel)
Sub count_light_errors(tabName)
Dim element As Variant
Dim formula As String
Dim temp As Variant
For Each element In tabName
temp = temp & "zählenwenn(" & element & "!E:E;""light"");"
Next element
formula = "=summe(" & Left(temp, Len(temp) - 1) & ")"
Worksheets("Overview").Range("C8").FormulaLocal = formula
End Sub
Version 2 (does not work with de-at Excel)
Sub count_light_errors(tabName)
Dim element As Variant
Dim formula As String
Dim temp As Variant
For Each element In tabName
temp = temp & "countif(" & element & "!E:E,""light""),"
Next element
formula = "=sum(" & Left(temp, Len(temp) - 1) & ")"
Worksheets("Overview").Range("C8").FormulaLocal = formula
End Sub
The difference between those two is just using the German words for
- SUM() -> SUMME()
- COUNTIF() -> ZÄHLENWENN()
and ; instead of , as seperator
How do I prepare formulas correctly, so they are independent from the user language pack?
Thanks a million!
-Claus
