Hello, first time poster here...

I've a question regarding the import of Excel data. I'm going to be
creating a flat file on a mainframe, copying it down to a PC and using
Excel to import it. I have a template that has cells and calculations
associated with it. Now, what I would like to do is to have the
mainframe prefix a record with a row / column coordinate, for example:

A1,Data for Cell A1
B1,Data for Cell B1
A2,Data for Cell A2
... etc.

This is a simple example, but the program on the mainframe will output
the cell address based upon some rules as it rolls through the data.
How can I translate the prefix into a cell adress telling Excel where
to load the value? I don't have a choice, the data is used for auditing
and has to fit in certain places.

If you could point me to some functions that would do this, I would be
grateful, my knowledge of Excel is fairly limited.

TIA,
DG


--
Data Gladiator
------------------------------------------------------------------------
Data Gladiator's Profile: http://www.excelforum.com/member.php...o&userid=30166
View this thread: http://www.excelforum.com/showthread...hreadid=498474

Anyone? <bump


--
Data Gladiator
------------------------------------------------------------------------
Data Gladiator's Profile: http://www.excelforum.com/member.php...o&userid=30166
View this thread: http://www.excelforum.com/showthread...hreadid=498474

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Dim testRng As Range
Dim curWks As Worksheet
Dim newWks As Worksheet

Set curWks = ActiveSheet
Set newWks = Worksheets.Add '(or any existing worksheet???)

With curWks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
Set testRng = Nothing
On Error Resume Next
Set testRng = newWks.Range(myCell.Value)
On Error GoTo 0
If testRng Is Nothing Then
myCell.Offset(0, 2).Value = "Invalid Address"
Else
myCell.Offset(0, 2).Value = "ok"
testRng.Value = myCell.Offset(0, 1).Value
End If
Next myCell
End Sub

I'm assuming that you've already imported the data into columns A and B. I put
a comment in column C if it worked/failed.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Data Gladiator wrote:

Hello, first time poster here...

I've a question regarding the import of Excel data. I'm going to be
creating a flat file on a mainframe, copying it down to a PC and using
Excel to import it. I have a template that has cells and calculations
associated with it. Now, what I would like to do is to have the
mainframe prefix a record with a row / column coordinate, for example:

A1,Data for Cell A1
B1,Data for Cell B1
A2,Data for Cell A2
.. etc.

This is a simple example, but the program on the mainframe will output
the cell address based upon some rules as it rolls through the data.
How can I translate the prefix into a cell adress telling Excel where
to load the value? I don't have a choice, the data is used for auditing
and has to fit in certain places.

If you could point me to some functions that would do this, I would be
grateful, my knowledge of Excel is fairly limited.

TIA,
DG

--
Data Gladiator
------------------------------------------------------------------------
Data Gladiator's Profile: http://www.excelforum.com/member.php...o&userid=30166
View this thread: http://www.excelforum.com/showthread...hreadid=498474

--

Dave Peterson