Plotting X-Y Coordinates On Excel Spreadsheet

alawrence

New Member
Joined
Feb 16, 2004
Messages
1
I have developed a spreadsheet to track and monitor scores. To the left-side of the spreadsheet there is a Y-axis with a scale ranging from <50 to 100 in increments of 5. My X-axis is along the top of the spreadsheet and contains the values, "Wk1", "Wk2","Wk3",..."Wk30".
I have manually placed "Xs" in the Excel cells to align with the score for a particular week. I have created different color patterns (e.g., 95-100 is blue, 80-90 is green, 65-75 is yellow, 55-60 is orange, and "<50-50" is red). The different color patterns is the reason I want to manually plot the "Xs". However, I do not know how to determine the x-y coordinates of the cells containing the "Xs".
I made a somewhat feeble attempt to record a macro that draws a line between the first two "Xs" so I could see what needed to be done to complete the macro for the entire set of values. In the macro, I highlighted the range of cells containiing the domain (Wk1) and searched for an "X". I went to the next column (Wk2) and searched for "X" again. I then drew a line connecting the two Xs.
I thought I could generalize this and repeat these steps until a line was drawn connecting each X from Wk1 to Wk30. However, I do not know how to determine the coordinates used in the command, ActiveSheet.Shapes.AddLine(169.5, 215.25, 199.5, 290.25). The values represent the first x coordinate, first y coordinate, second x coordinate and second y coordinate. The macro I created is shown below:

Sub TestDraw()
'
' TestDraw Macro
' Macro recorded 2/15/2004 by Art Lawrence
'
ActiveWindow.Visible = False
Windows("Plot Scores.xls").Activate
Range("E9:E20").Select
X1 = Selection.Find(What:="X", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Range("F9:F20").Select
X2 = Selection.FindNext(After:=ActiveCell).Activate
ActiveSheet.Shapes.AddLine(169.5, 215.25, 199.5, 290.25).Select
Selection.ShapeRange.Flip msoFlipHorizontal
End Sub


I appreciate any help anyone can give me.
Thanks,
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Hello Art,

Placing a line on a chart using the chart's coordinates is a bit tricky, so I won't try to describe it here. Instead, I have emailed you an example that shows how to do this. I hope it answers your question.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top