Converting Excel Cell Location into X and Y Coordinates

S

skulkrinbait

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.
 
S

skulkrinbait

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?
 
S

skulkrinbait

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.
 
N

NickHK

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top