VBA Plug data into the next available cell

[martyloo]

My code so far is.......

Sub Ace1_Click()
If IsEmpty(Range("B16")) Then
Range("B16") = 11
Else
Range("C16") = 11
End If
End Sub

All this does is if I press the relevant button "Ace1" it will display the value= 11 in cell B16, If i press this button again it will display the value=11 in cell C16, thats as far as my code goes.....

I would like it so if I press this button again it will display 11 in cell D16, if I press it again likewise cell E16, then cell F16... so on so forth

Not transferring the previous values. In the end if I click the button five times i want cells B16 C16 D16 E16 F16 all to have the value 11 in it.

Is this possible

 

[JackDanIce]

Didn't I just help you answer this?

 

[VoG]

Try

Sub Ace1_Click()
Dim NC As Integer
NC = WorksheetFunction.Max(2, Cells(16, Columns.Count).End(xlToLeft).Column + 1)
Cells(16, NC).Value = 11
End Sub

 

[martyloo]

Didn't I just help you answer this?

yeh it didnt work tho, it only plugged the data into the second cell then the button stopped working

 

[JackDanIce]

But then you put a second question about going to the next empty column (e.g. D, E, F etc) and I answered that too, so if you combined the two answers it would achieve what you're asking here now.

 

[martyloo]

try

sub ace1_click()
dim nc as integer
nc = worksheetfunction.max(2, cells(16, columns.count).end(xltoleft).column + 1)
cells(16, nc).value = 11
end sub

it works!!!!!!

 

[martyloo]

But then you put a second question about going to the next empty column (e.g. D, E, F etc) and I answered that too, so if you combined the two answers it would achieve what you're asking here now.

Oh really? I didnt't see, Thanks man I'll check again