Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine the text in a range that covers several cells
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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..... |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Determine if range has NO Blank Cells without looping through each cell in range | Excel Programming | |||
Combobox to determine text for a range of cells | Excel Programming | |||
Determine whether a value is in a range of cells | Excel Discussion (Misc queries) | |||
Referencing cells that array covers | Excel Programming | |||
Expanding Gray Bar That Covers Excel Cells | Excel Programming |