Calulated Columns - Formel wird nicht akzepiert: The formula contains a syntax error or is...

Hallo,
<br />ersetze in der Formel die Komma durch Semikolon (.
<br />Viele Grüße
<br />
<br />

Danke sehr!
<br />; ist wohl die Übersetzung von , im deutschen ;-)
<br />https://support.microsoft.com/en-us/office/if-function-7025be14-5665-43d0-af20-8293d1fe9d3a
<br />Wie kommt man da drauf?
<br />

Hi Mr. Korchok,

thank You for your answer. I try a translation of the problem.

If I open a word-file which includes a lot of equation fields the mathematic formula in these fields are not readable. The reason for this is that the formula are shown more often above each other in one raw. It seams that the formula are moved vertically
in steps to the top of the raw and all the steps are shown. A picture would show the situation very easily. So the formula is not readable. If I click with the mouse inside the raw - not in the field - and press the space-button once, all the formula in the
raw are shown correctly. If I slide the page outside the screen and then back, the formula is unreadable like before. By making a space once in the raw the formula is shown correctly again. The Problem is never shown if I print the file on paper or save it
in an pdf-file.

Do You have an idea how I could fix this problem?

Thank You. Yours Ritter

Large is another formula that looks for the highest value in a range.

The correct one is =UPPER

Nee, das geht so nicht. A) muss jedes " verdoppelt werden, weil " ist in VBA ein reserviertes Zeichen und B) musst Du FormulaLocal statt der FormulaR1C1Local nehmen.

Und mit ActiveCell.Offset(0, 10) würde ich niemals arbeiten, weil dann landet die Formel je nach aktueller Zelle irgendwo.

Schau Dir das Beispiel unten mal an und versuche es nach dem Schema nachzubauen.

Andreas.

Sub Example_HowToCreateAFormulaWithVBA()

 'Copy the formula from Excel as is

 ' =INDEX(C1:C2,MATCH($A$1,B1:B2,0))&"Whatever"

 'double each " sign inside:

 ' =INDEX(C1:C2,MATCH($A$1,B1:B2,0))&""Whatever""

 'surround the whole formula with " signs

 ' "=INDEX(C1:C2,MATCH($A$1,B1:B2,0))&""Whatever"""

 'Replace the cell references with a "placeholder" (any unique string that you like and doesn't exists elsewhere)

 ' "=INDEX(#C#,MATCH(#A#,#B#,0))&""Whatever"""

 'and assign to a string:

 Dim MyDefFormula As String

 MyDefFormula = "=INDEX(#C#,MATCH(#A#,#B#,0))&""Whatever"""

 

 'Now create the Range objects and refer to the real cells

 Dim a As Range, b As Range, c As Range

 Set a = Range("A1") 'A single cell

 Set b = Range("B2:B50") 'Multiple cells

 Set c = Range("C2", Range("C" & Rows.Count).End(xlUp)) 'From C2 to last used cell in column C

 'Note:

 ' You can refer to cells in an other sheet (note the dot in front of Range!):

 'With Worksheets("That")

 ' Set C = .Range("C2", .Range("C" & Rows.Count).End(xlUp))

 'End With

 ' Or other already opened files:

 'With Workbooks("This.xlsx").Worksheets("That")

 ' Set C = .Range("C2", .Range("C" & Rows.Count).End(xlUp))

 'End With

 

 'Make a copy of your default formula into a work string

 Dim MyFormula As String

 MyFormula = MyDefFormula

 'Replace the placeholders with the cell references

 MyFormula = Replace(MyFormula, "#A#", a.Address)

 'Note:

 ' The first arguments of Range.Address controls where the $ appears in the formula

 MyFormula = Replace(MyFormula, "#B#", b.Address(0, 0))

 'Note:

 ' The part 'External:=True' is necessary if you refer to other sheets or files

 ' But it works also if the reference is in the active sheet

 MyFormula = Replace(MyFormula, "#C#", c.Address(External:=True))

 

 'Write the formula into the cell

 Range("A2").Formula = MyFormula

 

 'Note:

 ' If your locale language is not English you can use the FormulaLocal property instead

 ' But in this case the code works only on PCs with your locale settings

 'Range("A2").FormulaLocal = MyFormula

 

 'Note:

 ' If you want to create array formulas use the FormulaArray property

 ' But in this case use must use English formulas, a FormulaArrayLocale doesn't exists

 'Range("A2:A10").FormulaArray = MyFormula

End Sub