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

Hi,

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

Mike

"Gotroots" wrote:

> 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
>

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
--
Cheers,
Ryan


"Gotroots" wrote:

> 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
>

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.

--
Rick (MVP - Excel)


"Gotroots" <(E-Mail Removed)> wrote in message
news:813BD982-DF18-4F4B-B516-(E-Mail Removed)...
> 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
>

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

"Mike H" wrote:

> Hi,
>
> Rng isn't selecting because it is never set to anything so it will fail.
> What do you want Rng to be?
>
> Mike
>
> "Gotroots" wrote:
>
> > 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
> >

"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.

--
Rick (MVP - Excel)


"Gotroots" <(E-Mail Removed)> wrote in message
news:8BFA1154-7F77-4D6F-A811-(E-Mail Removed)...
> 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
>
> "Mike H" wrote:
>
>> Hi,
>>
>> Rng isn't selecting because it is never set to anything so it will fail.
>> What do you want Rng to be?
>>
>> Mike
>>
>> "Gotroots" wrote:
>>
>> > 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
>> >

I ran your code and the last cell did not select.
On my last post I gave an example of what I hoped the code should do, that
is "B23" would be selected after the code had run.



"Ryan H" wrote:

> 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
> --
> Cheers,
> Ryan
>
>
> "Gotroots" wrote:
>
> > 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
> >

Hi

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

"Rick Rothstein" wrote:

> "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.
>
> --
> Rick (MVP - Excel)
>
>
> "Gotroots" <(E-Mail Removed)> wrote in message
> news:8BFA1154-7F77-4D6F-A811-(E-Mail Removed)...
> > 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
> >
> > "Mike H" wrote:
> >
> >> Hi,
> >>
> >> Rng isn't selecting because it is never set to anything so it will fail.
> >> What do you want Rng to be?
> >>
> >> Mike
> >>
> >> "Gotroots" wrote:
> >>
> >> > 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
> >> >
>
> .
>

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


"Rick Rothstein" wrote:

> 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.
>
> --
> Rick (MVP - Excel)
>
>
> "Gotroots" <(E-Mail Removed)> wrote in message
> news:813BD982-DF18-4F4B-B516-(E-Mail Removed)...
> > 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
> >
>
> .
>