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 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]

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

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