Macro to edit a scatter plot

[ranafast]

Hi,

I have a list of interest rates and durations and was hoping to be able to add in a third dimension of data which would either increase the size of the plotted points or change their colour depending on the rating. So have the normal scatter plot then save me from editing each point manually.

i.e.

A1	B1	C1	D1
Interest Rate	Duration	Rating
3%	3.5	A+
3%	3	A
4%	5	A+
2%	2	AA+
5%	10	A

<TBODY>
</TBODY>

 

[guibgirl86]

The best way I have worked on this is to sort the information into groups and plot them as separate sets which can be different colors.

Dim x As Long, y As Long, z As Long, rng1 As Range
                y = 2
                z = 2
                n = Cells(Rows.Count, "A").End(xlUp).Row 'count of all data present
For x = 2 To n Step 1
                    Set rng1 = Union(Range ("A" &x), Range("B" & x), Range("C" & x))
                            If Cells(x, 3).Value = "A+" Then
                                rng1.Copy Range("G" & y)
                                y = y + 1
                            ElseIf Cells(x, 3).Value = "A" Then
                                rng1.Copy Range("K" & z)
                                z = z + 1
                            End If
                Next x

This code allows you to sort through all of your data and copies the values into groups next to your data (you could also put it on another sheet just change

rng1.Copy Range("K" & z) to rng1.copy sheet(2).range("A" & z)

You just want to make sure you leave enough room for all the information to be pasted into the cells.