VB code alternative

[WAF875]

Private Sub CommandButton1_Click()
If Range("A1") = 0 Then
Range("A1") = 1
Exit Sub
End If
If Range("A1") > 0 Then
Range("B1") = 1
Exit Sub
End If
If Range("B1") > 0 Then
Range("C1") = 1
Exit Sub
End If
If Range("C1") > 0 Then
Range("D1") = 1
Exit Sub
End If
End Sub

I have tried the above code to input"1" to the next cell each time I click the button but it only does A1 and B1 then stops could someone explain why and maybe come up with an alternative Thanks

 

[jproffer]

It stops because you told it to.

If A1 > 0 Then B1 = 1...exit sub.

You want it to put one in the next cell that don't have one each time you click the button? Maybe:

[SIZE=1]Private Sub CommandButton1_Click()
Dim LastCol as Long
LastCol = Range("A1").End(xlToRight).Column
If LastCol > 4 Then Exit sub
Range("A" & LastCol + 1).Value = 1
End Sub[/SIZE]

That is totally untested as I have no excel at home, but I'm pretty sure that's what you need. Only line I'm not 100% about is defining the LastCol variable.

 

[steve the fish]

This will start from B1 and then put a 1 in C1, D1 etc. (provided the rest of the row is blank). If you need it to start at A1 it needs a slight change.

lr = Cells(1, Columns.Count).End(xlToLeft).Column
Cells(1, lr + 1) = 1

 

[WAF875]

This will start from B1 and then put a 1 in C1, D1 etc. (provided the rest of the row is blank). If you need it to start at A1 it needs a slight change.

lr = Cells(1, Columns.Count).End(xlToLeft).Column
Cells(1, lr + 1) = 1

Thanks Steve
This is way beyond my excel knowledge but it works fine. I've noticed it starts after the last populated cell on the row inputted (1) so I should be able to work it in okay as I can't figure out how to make it start anywhere else.
Thanks Again
Alan

 

[WAF875]

Thanks for the code, I tried it but it doesn't seem to do anything
Thanks
Alan

 

[WAF875]

It stops because you told it to.

If A1 > 0 Then B1 = 1...exit sub.

You want it to put one in the next cell that don't have one each time you click the button? Maybe:

[SIZE=1]Private Sub CommandButton1_Click()
Dim LastCol as Long
LastCol = Range("A1").End(xlToRight).Column
If LastCol > 4 Then Exit sub
Range("A" & LastCol + 1).Value = 1
End Sub[/SIZE]

That is totally untested as I have no excel at home, but I'm pretty sure that's what you need. Only line I'm not 100% about is defining the LastCol variable.

Thanks for the code but it doesn't seem to do anything when I click the button
Thanks Alan

 

[jproffer]

Yea, after a second look, I see why...

Cells(LastCol + 1, 1).Value = 1 in place of Range

Sorry about that. Glad Steve's code worked out for you

 

[WAF875]

Yea, after a second look, I see why...

Cells(LastCol + 1, 1).Value = 1 in place of Range

Sorry about that. Glad Steve's code worked out for you

Thanks
Alan