getting string to resolve to variable

[nathan2314]

hello all,

I'm trying to run a for loop and have the index combined with a string (either x or y) to loop through a bunch of number values and compare x1 with y1, x2 with y2, x3 with y3 etc...Then I want x line in my graph I have to turn red if x drops below the y line (that is the x < y for the values i'm comparing as the loop iterates.
[VBA]For i = 1 To 4
val1 = CStr("x" & i)
val2 = CStr("y" & i)

If val1 < val2 Then
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.ColorIndex = 3
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 3
.Shadow = False
End With

End If
Next i[/VBA]
The problem is that when I use the cstr() to combine x or y to the index, it ends up comparing for example "x1" < "y1". The variables x1 and y1 have already been assigned values earlier in the code. But in the loop it doesn't resolve x1 and y1 but compares just as strings. How can i get the strings x1, y1 to resolve to the variable values??

[figment]

you need to redefine your x and y values as arrays, here is an example

[vba]sub testing
dim x(1 to 4), y(1 to 4)
x(1)=1
x(2)=2
x(3)=3
x(4)=4
y(1)=4
y(2)=3
y(3)=2
y(4)=1

For i = 1 To 4

If x(i) < y(i) Then
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.ColorIndex = 3
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 3
.Shadow = False
End With

End If
Next i

end sub[/vba]

[mikerickson]

You'ld could set up X and Y as arrays and compare X(i) and Y(i).

An alternate approach would be to modify your current code that assignes values to the variables x1,x2,x3,x4 y1,y2,y3,y4 to take notice when the value it assignes to an xi variable is less than the variable it assigns to the corresponding yi variable.

[mdmackillop]

Similar thoughts here
[VBA]
Dim x1, x2, x3, x4
Dim y1, y2, y3, y4
xarr = Array(x1, x2, x3, x4)
yarr = Array(y1, y2, y3, y4)
For i = 1 To 4
val1 = xarr(i)
val2 = yarr(i)
If val1 < val2 Then

[/VBA]

[nathan2314]

Thanks All! The array thing worked great. Dont know why I didn't think of that....