I am opening an XML file in Excel 2002 and performing some data
manipulation (via macros) in Excel before linking the newly saved Excel
file to Microsoft Access 2000.

Once the XML file is opened in Excel, it displays all table headings in
numerous columns on the first row. For example, a table heading could
be: "/PROJECT/AREAS/TEXTEXEC1". All data for this heading begins in
this column on the second row and so forth. While all XML files that I
use will have the tableheadings that I need, there are certain XML
files that will not list tableheadings is there is no data associated
with that particular project file. As a result,
"/PROJECT/AREAS/TEXTEXEC1" could be located in column Z for one file
and column AB in another. This is where I have a problem. The macros
written by the Record Macro function uses column (range) references
when I actually need it to refer to cursor positions relative to
"/PROJECT/AREAS/TEXTEXEC1". Since the column where this dataset is
located may vary between Excel files, I need to build my references to
cursor positions in relation to the cell that /PROJECT/AREAS/TEXTEXEC1
is located. In addition, I am trying to Text to Columns that data in
that column and I don't know how via Macros to anchor the cursor and
highlight a range of say 500 rows.

The following is my current macro with range references that I need to
convert to reference off my tableheading:

Cells.Find(What:="/PROJECT/AREAS/FOLDER/AREA/STEP/TEXTEXEC2", After:= _
ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart,
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False,
SearchFormat:=False).Activate
Range("AO1").Select
Selection.EntireColumn.Insert
Range("AN2:AN578").Select
Selection.TextToColumns Destination:=Range("AN2"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False,
Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1)),
TrailingMinusNumbers:=True
Range("AN1").Select
Selection.AutoFill Destination:=Range("AN1:AO1"), Type:=xlFillDefault
Range("AN1:AO1").Select

******
Any Help would be appreciated!!!

Jeff

Use a Range variable in your Find statement

Dim rFound as Range

'rFound will be the cell that contains the results of the Find
Set rFound = ActiveSheet.Cells.Find(What:=....)

If Not rFound Is Nothing Then 'it found the cell

'reference cells relative to rFound
rFound.Offset(1,1).Value = 1 'set the value one row down
'and one to the right to
1

'Resize rFound
rFound.Resize(500) 'resize rFound from one row to 500

End If

Generally, you don't want to select a range and then do something to it.
Just refer to the range and do the action. Here are some more specific
examples

To insert a column to the right of rFound

rFound.Offset(0,1).EntireColumn.Insert

To TTC all the data in the same column as rFound

rFound.Parent.Range(rFound, rFound.End(xlDown)).TextToColumns...

So use Find to establish rFound, then use End, Offset, and Resize to
reference the ranges you need. Post back if you have specific questions
when you get into it.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com


Jeff wrote:
I am opening an XML file in Excel 2002 and performing some data
manipulation (via macros) in Excel before linking the newly saved
Excel file to Microsoft Access 2000.

Once the XML file is opened in Excel, it displays all table headings
in numerous columns on the first row. For example, a table heading
could be: "/PROJECT/AREAS/TEXTEXEC1". All data for this heading
begins in this column on the second row and so forth. While all XML
files that I use will have the tableheadings that I need, there are
certain XML files that will not list tableheadings is there is no
data associated with that particular project file. As a result,
"/PROJECT/AREAS/TEXTEXEC1" could be located in column Z for one file
and column AB in another. This is where I have a problem. The macros
written by the Record Macro function uses column (range) references
when I actually need it to refer to cursor positions relative to
"/PROJECT/AREAS/TEXTEXEC1". Since the column where this dataset is
located may vary between Excel files, I need to build my references to
cursor positions in relation to the cell that /PROJECT/AREAS/TEXTEXEC1
is located. In addition, I am trying to Text to Columns that data in
that column and I don't know how via Macros to anchor the cursor and
highlight a range of say 500 rows.

The following is my current macro with range references that I need to
convert to reference off my tableheading:

Cells.Find(What:="/PROJECT/AREAS/FOLDER/AREA/STEP/TEXTEXEC2", After:=
_ ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart,
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False,
SearchFormat:=False).Activate
Range("AO1").Select
Selection.EntireColumn.Insert
Range("AN2:AN578").Select
Selection.TextToColumns Destination:=Range("AN2"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False,
Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1)),
TrailingMinusNumbers:=True
Range("AN1").Select
Selection.AutoFill Destination:=Range("AN1:AO1"), Type:=xlFillDefault
Range("AN1:AO1").Select

******
Any Help would be appreciated!!!