I'm using Excel to create a screen designer utility with a VBA macro
which will take the screen once designed and output source code which
can then be compiled to actually display the screen in the environment
it is designed for.

One problem I have is I need to convert the positions of the various
fields from their Excel format ie "C3" into x and Y coordinates, so
foir example "I14" becomes 9,11. I'm using a range loop to go through
the screen picking out the fields:

For Each sMainScreen In Range("C4:CA21")

.............

Next sMainScreen

But how can I write something to work out each location as it finds
the fields?

Thanks for any assistance, hope it's clear enough.

Maybe it wasn't clear enough.

In essence what I need to know is what cell is being processed at each
stage of the loop. Is there anyway of finding that out?

I can then somehow split the range (eg D11) into it's x and y
components and work out the x and y coordinates.

Anyone know if this can be done?

I've managed to achieve my objective with a bit of a fudge, just
having x and y variables which I increment accordingly after each pass
of the loop, or every 80 passes for the y coord.

Is this what you mean ?

With Range("A1")
Debug.Print .Left, .Top
Debug.Print .Width, .Height
End With

You would also need to allow for the Application.WindowState and possibly
position, along that of the Window.

Or if you mean using a For Each loop on a Range:

Dim Cell As Range

For Each Cell In Range("B3:G15")
With Cell
Debug.Print "(" & .Row & "," & .Column & ") = " & .Value
End With
Next

NickHK

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I'm using Excel to create a screen designer utility with a VBA macro
> which will take the screen once designed and output source code which
> can then be compiled to actually display the screen in the environment
> it is designed for.
>
> One problem I have is I need to convert the positions of the various
> fields from their Excel format ie "C3" into x and Y coordinates, so
> foir example "I14" becomes 9,11. I'm using a range loop to go through
> the screen picking out the fields:
>
> For Each sMainScreen In Range("C4:CA21")
>
> ............
>
> Next sMainScreen
>
> But how can I write something to work out each location as it finds
> the fields?
>
> Thanks for any assistance, hope it's clear enough.
>