| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell not selecting
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cell not selecting
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cell not selecting
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cell not selecting
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" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cell not selecting
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cell not selecting
"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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cell not selecting
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cell not selecting
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 . |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cell not selecting
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" wrote in message ... 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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Paste data of a cell to another cell by just selecting the source | Excel Programming | |||
| Excel 2007 single cell selecting muliple cell | Excel Worksheet Functions | |||
| Selecting a cell entry based on cell validation selection | Excel Worksheet Functions | |||
| Transfer cell values to another cell by selecting button. | Excel Worksheet Functions | |||
| Selecting 10 charcters on one cell and pasting to another cell | Excel Programming | |||
Linear Mode
