Cell not selecting

[Gotroots]

The last blank cell containing the value of "B9" does not rng.Select. Much
appreciate if someone could advise me as to why.

If IsEmpty(Range("b9")) Then
MsgBox "No record found in B9.", vbInformation
Else
lngLastRow = Cells(Rows.Count, "B").End(xlUp).Row + 1

If lngLastRow <= 10 Then
Range("B10").Value = Range("B9").Value
Else
Cells(lngLastRow, "B").Value = Range("B9").Value
If rng Is Nothing Then
Else
rng.Select
End If
End If
End If

End Sub

 

[Mike H]

Hi,

Rng isn't selecting because it is never set to anything so it will fail.
What do you want Rng to be?

Mike

 

[Ryan H]

Your problem is you haven't assigned anything to rng so rng will always be
Nothing until you do assign it something. Do you have some code missing from
your post? Are you wanting to select the last cell, if not, which cell are
you wanting to select? Try the code below.

Note: I would recommend you put Option Explicit at the top of your module
which will force declaration of all variables and ensure you have not spelled
anything incorrectly.


Option Explicit

Sub RangeSelect()

Dim lngLastRow As Long
Dim rng As Range

If IsEmpty(Range("B9")) Then
MsgBox "No record found in B9.", vbInformation
Else
lngLastRow = Cells(Rows.Count, "B").End(xlUp).Row + 1

If lngLastRow <= 10 Then
Range("B10").Value = Range("B9").Value
Else
Cells(lngLastRow, "B").Value = Range("B9").Value
If Not rng Is Nothing Then
rng.Select
End If
End If
End If

End Sub

 

[Rick Rothstein]

You should really stay with one thread... it would have been better if you
posted this question back in your original thread where you got some of this
code from.

 

[Gotroots]

Hello,

I want rng to be the cell the value of “B9†is pasted into.

For example:
“Test Data†is in “B9†and when the code is run “Test Data†is inserted into
“B23†the first blank cell in “B10:Bâ€

When you say “it is never set to anything†how should this be done.

Gotroots

 

[Rick Rothstein]

"rng" is not a pre-defined entity in Excel's VBA (the way Selection or
ActiveCell is, for example), so VB assumes it is a variable. Variables need
to be assigned values. Since you are attempting to Select it, VB assumes it
to be a object and objects have a value of Nothing until they are set to
reference some actual object (such as a Range, Worksheet, etc.). You set an
object variable to a reference using the Set keyword, such as like this...

Set rng = Range("B9")

However, for what I see your code doing, I think you can bypass using a
"rng" variable and just select the range you just found...

Cells(lngLastRow, "B").Select

Put this line right after the line in your originally posted code where you
assign the contents of B9 to it.

 

[Gotroots]

I ran your code and the last cell did not select.

Your problem is you haven't assigned anything to rng so rng will always be
Nothing until you do assign it something. Do you have some code missing from
your post? Are you wanting to select the last cell, if not, which cell are
you wanting to select? Try the code below.

Note: I would recommend you put Option Explicit at the top of your module
which will force declaration of all variables and ensure you have not spelled
anything incorrectly.


Option Explicit

Sub RangeSelect()

Dim lngLastRow As Long
Dim rng As Range

If IsEmpty(Range("B9")) Then
MsgBox "No record found in B9.", vbInformation
Else
lngLastRow = Cells(Rows.Count, "B").End(xlUp).Row + 1

If lngLastRow <= 10 Then
Range("B10").Value = Range("B9").Value
Else
Cells(lngLastRow, "B").Value = Range("B9").Value
If Not rng Is Nothing Then
rng.Select
End If
End If
End If

End Sub

 

[gotroots]

Hi

Thank you for the clarity, I have to rush off so will test your suggestion
out tomorrow. Many thanks.

 

[Gotroots]

The last blank cell is now selecting only the cell remains without a value.
It should contain the value taken from "B9"

here is the code as it looks now:

If IsEmpty(Range("b9")) Then
MsgBox "No record found in B9.", vbInformation
Else
lngLastRow = Cells(Rows.Count, "B").End(xlUp).Row + 1

If lngLastRow <= 10 Then
Range("B10").Value = Range("B9").Value
Else
Cells(lngLastRow, "B").Select
If Not rng Is Nothing Then
rng.Select
End If
End If
End If

Gotroots