How to Select a relative range with Using "Find" and Offset()

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

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" <[email protected]> wrote in message
news:[email protected]...
| 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

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" <[email protected]> wrote in message
> news:[email protected]...
> | 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
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
| 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" <[email protected]> wrote in message
| > news:[email protected]...
| > | 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
| >
| >
| >

Jim we are getting real close!

I found the following loop via Google Search
Believe me I am trying to get this on my own also.


Sub Test4()
Dim LastDataColumn As Integer
Dim LastDataRow As Integer

LastDataColumn = 5 'Start point
' the above number should be the column number of
' Cells.Find(What:="IMPACTED ACCOUNTS").Offset(1,3)
'
Do While Not IsEmpty(Rows(34).Cells(LastDataColumn))
' Rows(34) above should be the row number of
' Cells.Find(What:="IMPACTED ACCOUNTS").Offset(1,3)
'
LastDataColumn = LastDataColumn + 1
Loop
LastDataColumn = LastDataColumn - 1

LastDataRow = 34 'Start point
' the above number should be the Row number of
' Cells.Find(What:="IMPACTED ACCOUNTS").Offset(1,3)
'
Do While Not IsEmpty(Columns(5).Cells(LastDataRow))
' Rows(34) above should be the row number of
' Cells.Find(What:="IMPACTED ACCOUNTS").Offset(1,3)
'
LastDataColumn = LastDataRow + 1
Loop
LastDataRow = LastDataRow - 1

End Sub

The above give me the LastDataColumn and the LastDataRow, or, the bottom
right of my range

Cells.Find(What:="IMPACTED ACCOUNTS").Offset(1,3)
(If I have the syntax correct) should give me the upper left of my range.

How do I get these parameters to place into your "line"

Set MyRange = MyRange.End(xlDown).End(xlToRight).Offset(1, 3)
effectively MyRange.MyLastRow.MyLastColumn.Offset(1,3)
????

Dennis


******************************************************

"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" <[email protected]> wrote in message
> news:[email protected]...
> | 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" <[email protected]> wrote in message
> | > news:[email protected]...
> | > | 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
> | >
> | >
> | >
>
>
>

Jim, below is corrected for a description error.

******************************************************

Sub Test4()
Dim LastDataColumn As Integer
Dim LastDataRow As Integer

LastDataColumn = 5 'First Column with meaningful data
' the above number should be the column number of
' Cells.Find(What:="IMPACTED ACCOUNTS").Offset(1,3)
'
Do While Not IsEmpty(Rows(34).Cells(LastDataColumn))
' Rows(34) above should be the row number of
' Cells.Find(What:="IMPACTED ACCOUNTS").Offset(1,3)
'
LastDataColumn = LastDataColumn + 1
Loop
LastDataColumn = LastDataColumn - 1

LastDataRow = 34 'First Row with meaningful data
' the above number should be the Row number of
' Cells.Find(What:="IMPACTED ACCOUNTS").Offset(1,3)
'
Do While Not IsEmpty(Columns(5).Cells(LastDataRow))
' Columns(5) above should be the row number of
' Cells.Find(What:="IMPACTED ACCOUNTS").Offset(1,3)
'
LastDataColumn = LastDataRow + 1
Loop
LastDataRow = LastDataRow - 1
Range("A1").Select
End Sub

The above give me the LastDataColumn and the LastDataRow, or, the bottom
right of my range
Cells.Find(What:="IMPACTED ACCOUNTS").Offset(1,3) should give me the
upper left of my range

How do I get these parameters to place into your "line"
Set MyRange = MyRange.End(xlDown).End(xlToRight).Offset(1, 3)
effectively MyRange.MyLastRow.MyLastColumn.Offset(1, 3)

And how do I get the initial start point integers for:
LastDataColumn (Initially the first data Column)
LastDataRow (Initially the first data Row)
out of "Cells.Find(What:="IMPACTED ACCOUNTS").Offset(1,3)" ??


Dennis
'*******************************************************

"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" <[email protected]> wrote in message
> news:[email protected]...
> | 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" <[email protected]> wrote in message
> | > news:[email protected]...
> | > | 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
> | >
> | >
> | >
>
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> | 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" <[email protected]> wrote in message
> | > news:[email protected]...
> | > | 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
> | >
> | >
> | >
>
>
>

!!!!<g>

--
Jim
"Dennis" <[email protected]> wrote in message
news:[email protected]...
| 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" <[email protected]> wrote in message
| > news:[email protected]...
| > | 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" <[email protected]> wrote in message
| > | > news:[email protected]...
| > | > | 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
| > | >
| > | >
| > | >
| >
| >
| >