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" wrote in message
...
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

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" wrote in message
...
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


.