!!!!<g

--
Jim
"Dennis" wrote in message
...
| 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
| |
| |
| |
|
|
|