Specifying Row / Column in Import File

[Data Gladiator]

Hello, first time poster here...

I've a question regarding the import of Excel data. I'm going to b
creating a flat file on a mainframe, copying it down to a PC and usin
Excel to import it. I have a template that has cells and calculation
associated with it. Now, what I would like to do is to have th
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 outpu
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 wher
to load the value? I don't have a choice, the data is used for auditin
and has to fit in certain places.

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

TIA,
D

 

[Data Gladiator]

Anyone? <bump

 

[Dave Peterson]

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