Loop Question

[ttropics]

Is it possible to to assign a button to complete 1 loop then a second click would perform the the next part of the loop?

 

[dreid1011]

I'm not sure if this is the most efficient way to do it, but I could imagine using a counter to keep track of the button clicks. And then have each section of code nested in an IF/Then or Select Case and choose which part of code you want to execute based on how many times the button has been clicked.

Sub Button_Click()
Dim ClickCount As Long
ClickCount = ClickCount + 1
If ClickCount = 1 Then
...first part of code here...
Else
If ClickCount = 2 Then
...second part of code here...
Else
.......
End If
End If
End Sub

 

[ttropics]

this is my code

Private Sub CommandButton1_Click()

Columns("B:C").ColumnWidth = 22


range("a1").Value = "Number"
range("b1").Value = "Square of the Number"
range("c1").Value = "Square root of the Number"


range("a2:c2").Value = 1


Dim x As Integer


For x = 2 To 17
Cells(x, 1).Interior.Color = RGB(178, 178, 178)
Cells(x, 1).Value = (x - 2) + 1
Cells(x, 2).Value = ((x - 2) + 1) ^ 2
Cells(x, 3).Value = ((x - 2) + 1) ^ (1 / 2)


Next x




End Sub

MY question wasnt very clear, what i need is for the first click to be x=2, the second to be x=3 and so
on.

thanks

 

[dreid1011]

Then perhaps something like this:

Private Sub CommandButton1_Click()

 Columns("B:C").ColumnWidth = 22


 range("a1").Value = "Number"
 range("b1").Value = "Square of the Number"
 range("c1").Value = "Square root of the Number"


 range("a2:c2").Value = 1


 Dim x As Integer

If x = 0 Then
   x = 2
Else
   x = x + 1
End If

 For x = 2 To 17
 Cells(x, 1).Interior.Color = RGB(178, 178, 178)
 Cells(x, 1).Value = (x - 2) + 1
 Cells(x, 2).Value = ((x - 2) + 1) ^ 2
 Cells(x, 3).Value = ((x - 2) + 1) ^ (1 / 2)


 Next x




 End Sub

After testing, you will have to move your "Dim x As integer" line outside of the sub as a global variable. Otherwise, it will just keep resetting it to 0 each time the macro runs. Or, you will have to keep track of it on a worksheet somewhere and add to the value each time the macro runs.

Dim x As Integer

Sub Click_Me()
If x = 0 Then
    x = 2
Else
    x = x + 1
End If
MsgBox x
End Sub