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