Jim,

I finally go it!

Thanks for the guidance Jim!

'The following VBA code finds a location in your worksheet
'[Sheets(1) in this case], you then manually create
'an Offset setting from the text-find:
'Cells.Find(What:="Your Choice of Text") to the actual data
'that you wish to utilize.

'Once the range "MyRange" is computed, another loop computes
'a Variable "TempVar" which represents the information in
'the "MyRange" cells in a Text variable that can be saved
'in another cell/Worksheet.

'This can be a great help to those doing SOX work where a
'great deal of data rollups occur.
'

Dennis

'************************************************* ******
Sub OneCellText()
'
'Assistance from Jim Rech 7/26/2005 Excel.General
'
Dim MyRange As Range
Dim MyCell As Range
Dim LastDataColumn As Integer
Dim LastDataRow As Integer
Dim FirstDataColumn As Integer
Dim FirstDataRow As Integer
' "Finds the 1st instance of the use of "IMPACTED
' ACCOUNTS" in the W/S and Offsets
' to the first cell with meaningful data
Set MyRange = ActiveBook.Sheets(1).Cells.Find _
(What:="IMPACTED ACCOUNTS").Offset(2, 3)
'Establishes the upperleft row number
FirstDataRow = MyRange.Row
LastDataRow = FirstDataRow
'Establishes the upperleft Column number
FirstDataColumn = MyRange.Column
LastDataColumn = FirstDataColumn
' Loop computes last column with data
Do While Not IsEmpty(Rows(FirstDataRow).Cells _
(LastDataColumn))
LastDataColumn = LastDataColumn + 1
Loop
'Represents the last column with meaningful data in
'the 1st meaningful row of data
LastDataColumn = LastDataColumn - 1
Do While Not IsEmpty(Columns(FirstDataColumn).Cells _
(LastDataRow))
LastDataRow = LastDataRow + 1
Loop
'Represents the last row with meaningful data in _
'the "MyRange" row of data
LastDataRow = LastDataRow - 1
'Establishes or "Sets" the Meaningful Data range
Set MyRange = Range(Cells(FirstDataRow, _
FirstDataColumn), Cells(LastDataRow, LastDataColumn))
For Each MyCell In MyRange
If MyCell.Value < "" Then TempVar = TempVar + _
MyCell.Value + Chr(10)
Next MyCell
'Clears any previous selections to A1
Range("A1").Select
End Sub

'************************************************* *********

"Jim Rech" wrote:

If I understand what you're asking perhaps...

Sub Test()
Dim MyRange As Range
Set MyRange = Cells.Find(What:="IMPACTED ACCOUNTS")
Set MyRange = MyRange.End(xlDown).End(xlToRight).Offset(1, 3)
MsgBox MyRange.Address ''Test - delete
End Sub

--
Jim
"Dennis" wrote in message
...
| Thanks for your time and knowledge Jim.
|
| Jim, what is the bst way to code the Range("A1:E10") would be relative, so
| that the "row" range would be .Offset(1, 3) to the last contiguious cell
with
| data (XLDown) and the last "column" would be from .Offset(1, 3) to the
last
| contiguious cell with data (XLRight)?
|
| Dennis
|
| "Jim Rech" wrote:
|
| It is almost always possible to write a macro that does what you want
| without "selecting". It's more efficient and more professional to do so
| and, in this case, it seems to have the additional benefit of working
around
| your problem. Here's an example:
|
| Sub aa()
| Dim MyRange As Range
| Set MyRange = Cells.Find(What:="IMPACTED ACCOUNTS")
| Set MyRange = MyRange.Offset(1, 3).Range("A1:E10")
| MsgBox MyRange.Address ''Test - delete
| End Sub
|
| You should be able to replace your Cells.Find/Select/Set MyRange =
Selection
| with code similar to this.
|
| --
| Jim
| "Dennis" wrote in message
| ...
| | XL 2003
| |
| | The following works fine:
| |
| | Sub OneCellText()
| |
| | Dim MyRange As Range
| | Dim MyCell As Range
| | Dim TempVar As String
| | Set MyRange = Selection
| |
| | For Each MyCell In MyRange
| | If MyCell.Value < "" Then TempVar = TempVar + MyCell.Value +
| Chr(10)
| | Next MyCell
| | Range("E41").Formula = TempVar
| | End Sub
| |
| | I would like to "compute" MyRange as follows:
| |
| | Cells.Find(What:="IMPACTED ACCOUNTS").Activate
| | ActiveCell.Offset(1, 3).Range("A1:E10").Select
| |
| | Where things get tough is that the W/S that I review have
| | merged cells in many places. Merged cells seem to screw
| | up what .Range("A1:E10") selects. (Meaning, if I un-merge the merged
| cells
| | then .Range("A1:E10") works perfect. But with merged the selection of
| | .Range("A1:E10") picks up a different range)
| |
| | Also, I am not sure how to formulate the ability of the macro to
select
| the
| | row range to include all rows from
| | ActiveCell.Offset(1, 3) (above) XLDown to the first cell that is
empty.
| In
| | addition, the Column range to move XLRight
| | to the first cell that is empty.
| |
| | In short, how do I,
| | 1)work around the merged cell vs. Offset()issue? (Note:
| | I can not change the merged cells (rights issue)
| |
| | 2)enhance the macro to compute the range to insert into
| | "MyRange" in the first macro?
| |
| | Thanks, Dennis
|
|
|