Range empty?

  • Thread starter Thread starter Sige
  • Start date Start date
S

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
 
Thx Norman!!!


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

"NOSPAM" to be removed for direct mailing...
 
Thx Norman!!!


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

"NOSPAM" to be removed for direct mailing...
 
Hi Sige,
PS: Were you able to trap the constants After the "*"-sign in my
previous thread?
Click to expand...

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

In summary:

Change:


to:

arr = Array("/", "~*", "+", "-", ">", "<", "=", "^", "[*]",
"(")
 
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...
 
Hi Sige,

Try:

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

Note the doubled apostrophes.
 
Hi Norman,

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

Though this one does not write the formula...

It beats me.
Sige
 
Hi Siege,

Please enter the formula in the worksheet manually and copy the resultant
formula into a reply.
 
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...
 
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
 
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
 
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?
 
Hi Sige,

Of course, the semi-colons may be correct with the 'IF' needing to be
changed to SE, SI, WENN, ALS...!
 
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.
 
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.
 
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.
Click to expand...
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 :o))))
Sige

PS: If have an outline issue coming up ...if interested, it will be in
a next thread
 
Actually, this comes up every so often. I just remembered previous posts (and a
simple test after changing my list separator).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy Last row UsedRange to next row 6
Copy Locked Cell Status 6
help with a sub 3
Code won't fill to last row in table 4
Followup To Jim Thomlinson macro 4
Set Range with Long Variable 2
Cell not selecting 8
creating sheets macro crashes after 26th sheet 6

Back
Top