Complex math - language issue

[cfuttrup]

Hi all.

I hope someone can help me out with the problem described here.

I've discovered that the complex math in Excel is badly implemented, since in english and danish, the different names means that the complex math stops working. For example we have:

English: IMSUB
Danish: IMAGSUB

and

English: IMPRODUCT
Danish: IMAGPRODUKT

If I create the spreadsheet in danish, it doesn't work in english version of Excel.

I thought I'd make a macro, which could search/replace these text strings. I'd place a
button and the user could choose language him-/herself. Just started looking into macros two days ago.

First I did a macro recording, which didn't continue to work. Simply because when I did the search/replace, the macro "forgets" to choose the entire workbook. It only searches the active sheet.

I continued by putting the words I want to search/replace into some cells. They are placed in Sheet "DK" in cell range B104-C104 as you can see below.

I coded the following:

Sub ToEnglish()
'
' ToEnglish Makro
' Makro indspillet 13-05-2010 af Claus Futtrup
'

'
    SaveActSheet = ActiveSheet.Name
    DK1 = Sheets("DK").Range("C103").Value
    DK2 = Sheets("DK").Range("C104").Value
    EN1 = Sheets("DK").Range("B103").Value
    EN2 = Sheets("DK").Range("B104").Value
    Sheets("5.47").Select
    Cells.Replace What:=DK1, Replacement:=EN1, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=DK2, Replacement:=EN2, LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Sheets(SaveActSheet).Select
End Sub
Sub ToDanish()
'
' ToDanish Makro
' Makro indspillet 13-05-2010 af Claus Futtrup
'

'
    SaveActSheet = ActiveSheet.Name
    DK1 = Sheets("DK").Range("C103").Value
    DK2 = Sheets("DK").Range("C104").Value
    EN1 = Sheets("DK").Range("B103").Value
    EN2 = Sheets("DK").Range("B104").Value
    Sheets("5.47").Select
    Cells.Replace What:=EN1, Replacement:=DK1, LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=EN2, Replacement:=DK2, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Sheets(SaveActSheet).Select
End Sub

Unfortunately the macro doesn't find anything to replace ... I am at a loss here. Running "BREAK" and "WATCH" the expressions DK1 and EN1 (stepping through the macro with F5) I can see that they stay empty ...

Please help me accomplish what I'd like to do.

Best regards,
Claus

[davegugg]

Maybe you could use the built-in find/replace function to just change those functions in the formulas in your cells:

Sub cfuttrup()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Sheets
    ws.Cells.Replace What:="IMSUB", Replacement:="IMAGSUM", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ws.Cells.Replace What:="IMPRODUCT", Replacement:="IMAGPRODUKT", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Next ws

End Sub

[cfuttrup]

Hi Dave.

Thanks. Your version works. It also seems relatively simple.

Is there any way in which I can change the "fixed" strings (IMAGSUB, IMSUB) into variables that I can load from some cells in the spreadsheet? I attempted to do that - just so that e.g. a German (or e.g. French) user could substitute the danish complex math functions for their own language ditto ...

Best regards,
Claus

[davegugg]

Yes, you can replace them with cells on a sheet, or you could use input boxes so the user types them directly.

Sub cfuttrup()

Dim ws As Worksheet
Dim str1 As String
Dim str2 As String
Dim str3 As String
Dim str4 As String

str1 = Sheets("Sheet Name").Cells(1, "A").Value
str2 = Sheets("Sheet Name").Cells(1, "B").Value
str3 = InputBox("Enter the text to replace.")
str4 = InputBox("Enter the replacement text.")

For Each ws In ActiveWorkbook.Sheets
    ws.Cells.Replace What:=str1, Replacement:=str2, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ws.Cells.Replace What:=str3, Replacement:=str4, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Next ws

End Sub

Examples of each is shown in the code above.

[squiggler47]

Thats interesting, I thought that excel files used a token system in <2007 versions, which would mean the function names should automatically convert between languages!

[cfuttrup]

Dear davegugg

Thank you very much for your help. I seem to run into the "missing defining the variable" ... because other code examples doesn't show this part. Your examples are most helpful.

Dear squiggler47

Yes it is interesting that complex math doesn't convert ... it's only a problem with the complex functions (which are activated by enabling Analysis ToolPak), AFAIK.

Best regards,
Claus