Force Data Entry???

[martyloo]

I have a problem, I have a number of buttons which once clicked assign a certain value to cell C10.

I have it so if i press another button or the same one twice it will recognise cell C10 is already occupied and therefor go to cell C11.

Thats as far as my code goes I am struggling as I need it, that if i press another button or the same button again for a third time it will recognise cell C10 and cell C11 are both occupied so go to cell C12..... likewise if I press a fourth button it will recognise cell C10, C11 and C12 are occupied so go to cell C13 and once more if i press a fifth button recognise the previous 4 cells have data in them so go to cell C14......

Can any one help???

 

[JackDanIce]

I'm assuming if C10 is filled, then you want C11 to be filled next, then C12 etc, e.g. sequentially.

However, later, if C11 is deleted do you want C13 filled or C11?

Anyway, try this:

IF IsEmpty(Range("C10")) Then
  Range("C10") = Range("A1")
  ' change =Range("A1") to whatever value you want in C10
Else
  Range("C10").End(xltoLeft).Offset(1,0) = Range("A1")
  ' as above, change =Range("A1") to whatever value you want in the next empty row in column C
End if

 

[Dannyh1]

Hi Try using OFFSET & LOOP

Something like

Range("C10").Select
If Selection.Value = "" Then
Range("C10").Select
Else
Do Until Selection.Value = ""
Range("C10").Select
Selection.Offset(1,0).Select
Loop
End If

 

[JackDanIce]

Apologies, error in my code, it should be:

IF IsEmpty(Range("C10")) Then
  Range("C10").select
Else
  Range("C10").End(xlDown).Offset(1,0).select
End if

This will be quicker than looping as well

 

[martyloo]

Hi,

When I press a button it displays the value 11 in C10, if i press it again it displays the value in D10 (sorry i made a mistake not C11).

I want it so if i press it again it goes to E10, if i press it again F10, if i press it again G10.

No cells would be deleted the only way to delete one cell would be to press the reset button which changes everything back to zero.

This is the 2nd time you have helped me JackDanIce thanks I really appreciate it.

any ideas???

 

[martyloo]

Apologies, error in my code, it should be:

IF IsEmpty(Range("C10")) Then
  Range("C10").select
Else
  Range("C10").End(xlDown).Offset(1,0).select
End if

This will be quicker than looping as well

Where do I display the value I want in this? after select would I write Value = 11

 

[JackDanIce]

You're welcome. Try this instead:

IF IsEmpty(Range("C10")) Then
  Range("C10").value = 11
Else
  Range("C10").End(xltoLeft).Offset(0,1).value = 11
End if

 

[martyloo]

doesn't work only fills in C10 then if i click it again C09 but it stops there, if i click the button again nothing happens

 

[JackDanIce]

Ah found an error with this. Try:

If IsEmpty(Range("C10")) Then
  Range("C10").Value = 11
Else
  Cells(10, Columns.Count).End(xlToLeft).Offset(0, 1) = 11
End If