PDA

View Full Version : getting string to resolve to variable



nathan2314
07-07-2008, 02:25 PM
:hi: 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.
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
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
07-07-2008, 03:02 PM
you need to redefine your x and y values as arrays, here is an example

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

mikerickson
07-07-2008, 03:03 PM
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
07-07-2008, 03:08 PM
Similar thoughts here

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

nathan2314
07-08-2008, 05:25 AM
:thumb
Thanks All! The array thing worked great. Dont know why I didn't think of that....