For and If Then loops, and remembering a value

A

Art

H
I have a test: For K=53 to 6 Step -1, If the value in a cell is >0, then add a chart. If the value in the cell is not >0, then reduce the column number by one and test again. Repeat until true. If it is true in column 45, I would like it to remember column 45 so that I can use that column reference later, but outside of that loop. I tried using "K", but the loop continues to run down to 5. How do I stop the loop from continuing, or can I? How do I remember column 45?

Thanks for the help. This is a great newsgroup

Art
 
B

Bob Kilmer

Create a variable of the type of what you want to remember about "column
45", then assign the variable the value of (or the reference to) "column 45"
when K is 45.

I am not sure what you want to remember about "column 45" but I'll assume it
is the 45th column itself.

Dim rCol45 as Range
For K=53 to 6 Step -1
If K = 45 and "a cell is >0" Then
Set rCol45 = Columns(45)
'etc.
End If
Next K

rCol45.Cells(1).Value = "X" 'asigns "X" to first cell in Columns(45)

If this doesn't help, clarify.

Bob


Art said:
Hi
I have a test: For K=53 to 6 Step -1, If the value in a cell is >0, then
Click to expand...
add a chart. If the value in the cell is not >0, then reduce the column
number by one and test again. Repeat until true. If it is true in column
45, I would like it to remember column 45 so that I can use that column
reference later, but outside of that loop. I tried using "K", but the loop
continues to run down to 5. How do I stop the loop from continuing, or can
I? How do I remember column 45?
 
R

Rick

Hi Art,

I'm not sure what you want either. Maybe this will help.
You exit the For Loop with "Exit For". There's so many
variations to what you request. Maybe you could help
clarify. Sometimes we write our ideas out with logical
pseudo-code...or just bad code to start things out.
Message boxes shorten things a bit too, so you can get the
concept to work first...then add details. To get it to
remember the column, you can just have it write out to a
cell. There's more you could do too....

Sub TestingCharts()
Dim K As Integer

For K = 53 To 6 Step -1 '53 to 6 are columns
If ActiveSheet.Cells(1, K).Value > 0 Then
MsgBox "Add A Chart...Column " & K
ActiveSheet.Cells(1).Value = _
ActiveSheet.Cells(1, K).Column
Exit For
End If
Next K

End Sub

Rick
-----Original Message-----
Hi
I have a test: For K=53 to 6 Step -1, If the value in a
Click to expand...
cell is >0, then add a chart. If the value in the cell is
not >0, then reduce the column number by one and test
again. Repeat until true. If it is true in column 45, I
would like it to remember column 45 so that I can use that
column reference later, but outside of that loop. I tried
using "K", but the loop continues to run down to 5. How
do I stop the loop from continuing, or can I? How do I
remember column 45?
 
A

Art

Thanks for the help. I'll use msgboxes a lot more often. That helped me enough there to see where I went wrong. Dumb little error elsewhere

Art
 
Top