!!!!<g>
--
Jim
| 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
| > | > | 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
| > | > | > | > | 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
| > | >
| > | >
| > | >
| >
| >
| >
