I think that if you're writing the formulas through code, then you use the
English versions of the functions and commas for the list separators.

It'll be converted to local settings when it hits the worksheet.

Using .formulalocal would require those changes, though.

Norman Jones wrote:

Hi Sige,

Of course, the semi-colons may be correct with the 'IF' needing to be
changed to SE, SI, WENN, ALS...!

---
Regards,
Norman

"Norman Jones" wrote in message
...
Hi Sige,

I ran your Sub Check_Usedrange() procedure and the required three IF
formulas were correctly inserted. The only change that I made to your sub
was to replace the semi-colons in the IF forrmula expressions with the
US/UK standard commas.

I assumed from your use of semi-colons that your local settings required
semi-colons, as would be the case if, for example, you were using a French
or Italian version of Excel. However, this would also require you to
change IF to SE or SI for those versions. I wonder, therefore, if you have
not simply used semi-colons in error.

So what happens if, like me, you change:

.Cells(lngLastRow + 1, 15).Formula = _
"=IF(" & rLastRowCell.Address(0, 0) & "="""";"""";$D$1)"
.Cells(lngLastRow + 1, 16).Formula = _
"=IF(" & rLastRowCell.Address(0, 1) & "="""";"""";$D$1)"
.Cells(lngLastRow + 1, 17).Formula = _
"=IF(" & rLastRowCell.Address(0, 2) & "="""";"""";$D$1)"

to:

.Cells(lngLastRow + 1, 15).Formula = _
"=IF(" & rLastRowCell.Address(0, 0) & "="""","""",$D$1)"
.Cells(lngLastRow + 1, 16).Formula = _
"=IF(" & rLastRowCell.Address(0, 1) & "="""","""",$D$1)"
.Cells(lngLastRow + 1, 17).Formula = _
"=IF(" & rLastRowCell.Address(0, 2) & "="""","""",$D$1)"

And, to resolve confusion, what are your version / country settings?

---
Regards,
Norman



"Sige" wrote in message
oups.com...
Hi Norman,

I cannot figure out what is wrong ...I take underneath sub ...plug it
into a new workbook...trow some numbers in it..... run it ...&:

I can write the "=$D$1"-functions but my IF-functions do not appear
anywhere!

But why? God knows ...
I will dream about it tonight!
Sige


Sub Check_Usedrange()
Dim lngLastRow As Long, lngLastCol As Long, j As Long
Dim rLastRowCell As Range
On Error Resume Next
lngLastRow = 1
With ActiveSheet.UsedRange
lngLastRow = .Find("*", .Cells(1), xlFormulas, xlWhole,
xlByRows, xlPrevious).Row

Set rLastRowCell = Cells(lngLastRow + 1, 6)
MsgBox rLastRowCell.Address
.Rows(lngLastRow).Copy
.Rows(lngLastRow + 1).PasteSpecial Paste:=xlPasteFormats
.Rows(lngLastRow + 1).PasteSpecial Paste:=xlPasteFormulas

For j = 1 To .Cells(lngLastRow + 1,
Columns.Count).End(xlToLeft).Column
If Not .Cells(lngLastRow + 1, j).HasFormula Then
.Cells(lngLastRow + 1, j).ClearContents
End If
Next j

.Cells(lngLastRow + 1, 4).Formula = "=$D$1"
.Cells(lngLastRow + 1, 6).Formula = "=$D$1"
.Cells(lngLastRow + 1, 6).Font.Name = "Arial"
.Cells(lngLastRow + 1, 7).Formula = "=$D$1"
.Cells(lngLastRow + 1, 7).Font.Name = "Arial"
.Cells(lngLastRow + 1, 8).Formula = "=$D$1"
.Cells(lngLastRow + 1, 8).Font.Name = "Arial"
.Cells(lngLastRow + 1, 9).Formula = ""

.Cells(lngLastRow + 1, 15).Formula = "=IF(" &
rLastRowCell.Address(0, 0) & "="""";"""";$D$1)"
.Cells(lngLastRow + 1, 16).Formula = "=IF(" &
rLastRowCell.Address(0, 1) & "="""";"""";$D$1)"
.Cells(lngLastRow + 1, 17).Formula = "=IF(" &
rLastRowCell.Address(0, 2) & "="""";"""";$D$1)"
End With
End Sub




--

Dave Peterson