Range empty?
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
|