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
|