Range empty?

[Sige]

Hi There,

How comes Range = Nothing?
I would like the message box to show something like: F125

Thanks, Sige


Sub Show_Usedrange()
Dim lngLastRow 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

End With

End Sub

 

[Norman Jones]

Hi Sige,

Change:

MsgBox rLastRowCell

to:
MsgBox rLastRowCell.Address

 

[SIGE]

Thx Norman!!!


PS: Were you able to trap the constants After the "*"-sign in my
previous thread?

"NOSPAM" to be removed for direct mailing...

 

[SIGE]

Thx Norman!!!


PS: Were you able to trap the constants After the "*"-sign in my
previous thread?

"NOSPAM" to be removed for direct mailing...

 

[Norman Jones]

Hi Sige,

PS: Were you able to trap the constants After the "*"-sign in my
previous thread?

Yes see my reponse in that thread and the conversation with Jan Karel
Pieterse:

In summary:

Change:


to:

arr = Array("/", "~*", "+", "-", ">", "<", "=", "^", "[*]",
"(")

 

[Norman Jones]

Hi Sige,

Yes - see response in that thread.

 

[SIGE]

Any reason why then

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

Returns empty?

Brgds, 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, 15).Formula = "=IF(" &
rLastRowCell.Address(0, 0) & "="";"";$D$1)"
End With
End Sub

"NOSPAM" to be removed for direct mailing...

 

[Norman Jones]

Hi Sige,

Try:

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

Note the doubled apostrophes.

 

[Sige]

Hi Norman,

You are my today's hero ... !!!

Though this one does not write the formula...

It beats me.
Sige

 

[Norman Jones]

Hi Siege,

Please enter the formula in the worksheet manually and copy the resultant
formula into a reply.

 

[SIGE]

Hi Norman,

You mean that I just have the show you what kind of formula should be
written ..?

=IF(F12="";"";$D$1)

HTH Sige

"NOSPAM" to be removed for direct mailing...

 

[Norman Jones]

Hi Sige

I ran your sub on a test sheet using:

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

and successfully entered your required formula.

Your local settings require the use of semi-colons whereas the corresponding
US/UK character is a a comma. Therefore, for my test purposes, I replaced
the two IF formula semi-colons with commas.

Of course, if the relevant column F cell is empty, your column O formula
will return an empty string and, although present, will not be visible

 

[Sige]

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

 

[Norman Jones]

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?

 

[Norman Jones]

Hi Sige,

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

 

[Dave Peterson]

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]

Hi Dave,

Thank you!

That was something I once knew and had forgotten!

It also confirms my suggestion to Sige that he should try the comma
adaptation, so thnaks again.

 

[Sige]

And GOD spoke!

It works ... with the commas instead of the semi-colons in my code!

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.

My commas get converted to Semi-colons!!!


FYI: I have an english XL97 SR-2 but my country settings are
Dutch(Belgium)


Thx thx thx
So happy ))))
Sige

PS: If have an outline issue coming up ...if interested, it will be in
a next thread

 

[Dave Peterson]

Actually, this comes up every so often. I just remembered previous posts (and a
simple test after changing my list separator).