Putting co ordinates into excel cells using a macro

**helium** · 04-19-2012, 06:27 PM

I'm new to vba, so bare with me.

I'm not sure if this possible to do or not but what I would like to do is write a macro which will put in (relative reference) co ordinates into a selected range of cells.

For example: If I select the 3x3 range A1:C3 then I want to be able to run a macro which will put the x,y coordinate (0,0) in the middle cell of the range (cell B2) and give coordinates based around this: i.e. A1 = (-1, 1), B1 = (0, 1), C1 = (1, 1), A2 = (-1, 0), B2 = (0, 0), C2 = (1, 0), A3 = (-1, 1), B3 = (0, -1), C3 = (1, -1).

Cheers

**shg** · 04-19-2012, 06:36 PM

How about just a formula? In A1 and copy across and down,

="(" & COLUMN() - COLUMN($B$2) & "," & ROW($B$2) - ROW() & ")"

**helium** · 04-19-2012, 07:17 PM

Love the formula. However is there way it could be modified so that the centre cell of the range is the coordinate (0,0)?

**helium** · 04-19-2012, 07:20 PM

For instance the formula you provided has $B$2 locked in so for the example I provided above it works perferctly.

But what about for different range?

**shg** · 04-19-2012, 07:32 PM

Change B2 to the center cell.

**helium** · 04-19-2012, 07:55 PM

Yip that's what I've ended up doing.

Cheers for your help!

**shg** · 04-19-2012, 08:15 PM

Please Login or Register  to view this content.

**helium** · 04-19-2012, 10:08 PM

I was helping someone out on another forum as I once had a problem similar to theirs.

At the moment I'm just starting out learning by looking at what the macro recorder does then modifying it...

Sorry for not cross posting: http://www.mrexcel.com/forum/showthr...95#post3123195