Determine the text in a range that covers several cells
 

I defined a name on a sheet that refers to the combined cells A1-A3. I
can set it to a text string but I can not read back what it contains.
Here is a snipit of my code that sets the value and then tries to test
what is stored in it.
I would appreciate any help in figuring out how to get the value of
this named range.
Thanks
Scott

Sub Test()
'LName is the combined range on the worksheet of cells A1 to A3
Worksheets("Sheet1").Range("LName") = "Anderson"
'Test to see what "LName" is
If Worksheets("Sheet1").Range(LName).FormulaR1C1 < "" Then MsgBox
"Yes"
If Worksheets("Sheet1").Range(LName).Value < "" Then MsgBox "Yes"
If Worksheets("Sheet1").Range(LName).Text < "" Then MsgBox "Yes"
If Worksheets("Sheet1").Range("LName").FormulaR1C1 < "" Then
MsgBox "Yes"
If Worksheets("Sheet1").Range("LName").Value < "" Then MsgBox
"Yes"
If Worksheets("Sheet1").Range("LName").Text(1) < "" Then MsgBox
"Yes"
End Sub

Determine the text in a range that covers several cells
 

wrote:
I defined a name on a sheet that refers to the combined cells A1-A3. I
can set it to a text string but I can not read back what it contains.
Here is a snipit of my code that sets the value and then tries to test
what is stored in it.
I would appreciate any help in figuring out how to get the value of
this named range.
Thanks
Scott

Sub Test()
'LName is the combined range on the worksheet of cells A1 to A3
Worksheets("Sheet1").Range("LName") = "Anderson"
'Test to see what "LName" is
If Worksheets("Sheet1").Range(LName).FormulaR1C1 < "" Then MsgBox
"Yes"
If Worksheets("Sheet1").Range(LName).Value < "" Then MsgBox "Yes"
If Worksheets("Sheet1").Range(LName).Text < "" Then MsgBox "Yes"
If Worksheets("Sheet1").Range("LName").FormulaR1C1 < "" Then
MsgBox "Yes"
If Worksheets("Sheet1").Range("LName").Value < "" Then MsgBox
"Yes"
If Worksheets("Sheet1").Range("LName").Text(1) < "" Then MsgBox
"Yes"
End Sub


Because of the inconsistency in your code, I am having a difficult time
trying to understand what you are trying to accomplish. Sometimes you
refer to your NamedRange as Range(LName), other times it is
Range("LName"). Which is it?
Range(LName) would refer to a variable, in other words you would have
had to do something like this earlier in your code...

Dim LName as Range
Set LName = Application.ActiveCell

Range("LName") on the other hand would refer to a NamedRange. I am
guessing that is what you mean based on what you said, but you better
fix the syntax in your code first before you continue asking questions.
That alone may fix your problem, check it out.

Determine the text in a range that covers several cells
 

Sub Test()
'LName is the combined range on the worksheet of cells A1 to A3
Worksheets("Sheet1").Range("LName") = "Anderson"
'Test to see what "LName" is
If Worksheets("Sheet1").Range(LName).FormulaR1C1 < "" Then MsgBox
"Yes"
If Worksheets("Sheet1").Range(LName).Value < "" Then MsgBox "Yes"
If Worksheets("Sheet1").Range(LName).Text < "" Then MsgBox "Yes"
If Worksheets("Sheet1").Range("LName").FormulaR1C1 < "" Then
MsgBox "Yes"
If Worksheets("Sheet1").Range("LName").Value < "" Then MsgBox
"Yes"
If Worksheets("Sheet1").Range("LName").Text(1) < "" Then MsgBox
"Yes"
End Sub


Because of the inconsistency in your code, I am having a difficult time
trying to understand what you are trying to accomplish. Sometimes you
refer to your NamedRange as Range(LName), other times it is
Range("LName"). Which is it?
Range(LName) would refer to a variable, in other words you would have
had to do something like this earlier in your code...

Dim LName as Range
Set LName = Application.ActiveCell

Range("LName") on the other hand would refer to a NamedRange. I am
guessing that is what you mean based on what you said, but you better
fix the syntax in your code first before you continue asking questions.
That alone may fix your problem, check it out.

Determine the text in a range that covers several cells
 

Mark wrote:
Sub Test()
'LName is the combined range on the worksheet of cells A1 to A3
Worksheets("Sheet1").Range("LName") = "Anderson"
'Test to see what "LName" is
If Worksheets("Sheet1").Range(LName).FormulaR1C1 < "" Then MsgBox
"Yes"
If Worksheets("Sheet1").Range(LName).Value < "" Then MsgBox "Yes"
If Worksheets("Sheet1").Range(LName).Text < "" Then MsgBox "Yes"
If Worksheets("Sheet1").Range("LName").FormulaR1C1 < "" Then
MsgBox "Yes"
If Worksheets("Sheet1").Range("LName").Value < "" Then MsgBox
"Yes"
If Worksheets("Sheet1").Range("LName").Text(1) < "" Then MsgBox
"Yes"
End Sub


Because of the inconsistency in your code, I am having a difficult time
trying to understand what you are trying to accomplish. Sometimes you
refer to your NamedRange as Range(LName), other times it is
Range("LName"). Which is it?
Range(LName) would refer to a variable, in other words you would have
had to do something like this earlier in your code...

Dim LName as Range
Set LName = Application.ActiveCell

Range("LName") on the other hand would refer to a NamedRange. I am
guessing that is what you mean based on what you said, but you better
fix the syntax in your code first before you continue asking questions.
That alone may fix your problem, check it out.


The code I have shown is what I have tried and not been successfull
with. I just gave it as a example. I know that it is wrong.
It seems that when refering to a range that spans multiple cells you
have to approach it differently?
Thanks
Scott

Determine the text in a range that covers several cells
 

Try this out:


Sub RangeTest()

Dim CurRng As Range
Dim CellRng As Range

Application.ScreenUpdating = False
Set CurRng = Range("A1:A3")
CurRng = "Anderson"
For Each CellRng In CurRng
MsgBox "Range (" & CellRng.Address & ") CurRng now contains the
value: " & CellRng
Next CellRng
Application.ScreenUpdating = True

End Sub

I think what is throwing you is that when you define a range that has
more than one cell in it, you are trying to ask the computer for the
value of the range as a whole. this cannot be done. You have to
iterate through each member of the range and get it's individual
contents. That is what the For/Next loop does in the routine above.
Hope that is more helpful.

Determine the text in a range that covers several cells
 

Mark wrote:
Try this out:


Sub RangeTest()

Dim CurRng As Range
Dim CellRng As Range

Application.ScreenUpdating = False
Set CurRng = Range("A1:A3")
CurRng = "Anderson"
For Each CellRng In CurRng
MsgBox "Range (" & CellRng.Address & ") CurRng now contains the
value: " & CellRng
Next CellRng
Application.ScreenUpdating = True

End Sub

I think what is throwing you is that when you define a range that has
more than one cell in it, you are trying to ask the computer for the
value of the range as a whole. this cannot be done. You have to
iterate through each member of the range and get it's individual
contents. That is what the For/Next loop does in the routine above.
Hope that is more helpful.

Part of my problem may be that the range I refer to is not a range with
3 cells. It is 3 cells that have been merged together and then had the
name applied to the merged cell.
It does appear that when I set the merged cell range to a value that
it is contained in the first cell of that range. I think I can get to
the value of it by just determining the row and column for the range
which is the first cell in that range. I should be able to get the
cells.text value then.

Scott

Determine the text in a range that covers several cells
 

Here is what I got to work finally. It is pretty simple now that I
understand it more but it took a bit to get to it.
Thanks for all the help Mark


Scott


Sub Test()
'LName is the merged range of cells, on the worksheet, of cells A1 to
A3
Worksheets("Sheet1").Range("LName") = "Anderson"
'Test to see what "LName" is
If Cells(Range("LName").Row, Range("LName").Column).Value < ""
Then MsgBox "Yes"
End Sub

Determine the text in a range that covers several cells
 

Hi Scott,

Try one cell at a time...

Sheet1.Range("LName").Value = "Anderson" makes all 3 cells = "Anderson"

To check...

MsgBox Sheet1.Range("LName").Cells(1,1).Value will return "Anderson"
as will Sheet1.Range("LName").Cells(2,1).Value and
Sheet1.Range("LName").Cells(3,1).Value

Ken Johnson

Determine the text in a range that covers several cells
 

Mark wrote:
Sub Test()
'LName is the combined range on the worksheet of cells A1 to A3
Worksheets("Sheet1").Range("LName") = "Anderson"
'Test to see what "LName" is
If Worksheets("Sheet1").Range(LName).FormulaR1C1 < "" Then MsgBox
"Yes"
If Worksheets("Sheet1").Range(LName).Value < "" Then MsgBox "Yes"
If Worksheets("Sheet1").Range(LName).Text < "" Then MsgBox "Yes"
If Worksheets("Sheet1").Range("LName").FormulaR1C1 < "" Then
MsgBox "Yes"
If Worksheets("Sheet1").Range("LName").Value < "" Then MsgBox
"Yes"
If Worksheets("Sheet1").Range("LName").Text(1) < "" Then MsgBox
"Yes"
End Sub

Try this one last time, slightly different

Sub SelectRange()

Dim CurRng As Range
Dim CellRng As Range

Application.ScreenUpdating = False
Set CurRng = Range("A1:A3")
CurRng = "Anderson"
For Each CellRng In Range("LName") 'CurRng
MsgBox "Range (" & CellRng.Address & ") LName now contains the
value: " & CellRng
Next CellRng
Application.ScreenUpdating = True

End Sub

I used this with a merged range as you stated. It appears that when
addressing a NamedRange that is Merged that only the First cell in the
range (Upper LeftMost) will contain the text value that has been
entered into it. That being the case, you might try testing your
values with something like this:

If Range("LName").Cells(1,1) < "" Then
MsgBox "Do Something"
End If

Hope we are getting closer to what you need.....

Determine the text in a range that covers several cells
 

Sub Test()
'LName is the merged range of cells, on the worksheet, of cells A1 to
'A3
Worksheets("Sheet1").Range("LName") = "Anderson"
'Test to see what "LName" is
If Range("LName")(1).Value < "" Then MsgBox "Yes"
End Sub

would be simpler.

--
Regards,
Tom Ogilvy




" wrote:

Here is what I got to work finally. It is pretty simple now that I
understand it more but it took a bit to get to it.
Thanks for all the help Mark


Scott


Sub Test()
'LName is the merged range of cells, on the worksheet, of cells A1 to
A3
Worksheets("Sheet1").Range("LName") = "Anderson"
'Test to see what "LName" is
If Cells(Range("LName").Row, Range("LName").Column).Value < ""
Then MsgBox "Yes"
End Sub

Determine the text in a range that covers several cells
 

That looks much simplier. I know there had to be some way to refer to
it better.

Range("LName")(1).Value

Thanks Tom.