VBA Simple If

[Mike54]

Need a bit of help here if possible I am new to VBA. I have been searching the "Excel Questions" but can't seem to find any clues. All this is supposed to do is to select a cell and if empty paste in the date/time but I keep geting Compile error where am I going wrong. Many thanks Mike54.


Private Sub CommandButton1_Click()
Dim X As Integer
Dim Y As Integer
X = 2
Y = 2
Cells(X, Y).Select
If Cells.Value = "" Then Cells(X, Y).Value = Now() Else: X = X + 1

End If
End Sub

 

[njimack]

The following worked for me:

Private Sub CommandButton1_Click()
Dim X, Y As Integer

X = 2
Y = 2

If Cells(X, Y).Value = "" Then
Cells(X, Y).Value = Now()
Else
X = X + 1

End If
End Sub

Note: there's no need to select the cells

 

[Mike54]

Thanks for that your quite right it worked well, and thanks for the tip re "no need to select".
This learning process is slow but addcitive.
Cheer
Mike54

 

[Peter_SSs]

Mike54

Why the X=X+1? The code does nothing else after performing this increase. Are you trying to loop through a range of cells? What range?

 

[Mike54]

Thanks for asking, I was trying to make it so that each time I pressed the command button it would select the next cell and post new information (assuming the cell was vacant) a sort of "Timestamp"
I think I may have to use some sort of loop.
Mike54

 

[Peter_SSs]

Thanks for asking, I was trying to make it so that each time I pressed the command button it would select the next cell and post new information (assuming the cell was vacant) a sort of "Timestamp"
I think I may have to use some sort of loop.
Mike54

You might need to spell out in more detail exactly what is to happen and when. In particular the comment about "if the next cell is empty". Are we just talking about column B? If so, are there already some cells scattered down column B that have data in them?

Any chance of seeing a sample from your sheet and refer your macro explanation to that sample?

 

[Mike54]

Well basically I'm starting with a blank sheet, if I could get it to work I would incorporate it into a sports score sheet ie if player 8 scored a goal or a try. The user would click on the button and it would add a date/time stamp in say column A.
I was only asking it to check to see if it was blank so that it was okay to paste the data if not move to the next cell and stamp that.
Clearly I would need to ensure that there was sufficient space in the column.
Thanks for your time.
Mike54

 

[Peter_SSs]

Well basically I'm starting with a blank sheet, if I could get it to work I would incorporate it into a sports score sheet ie if player 8 scored a goal or a try. The user would click on the button and it would add a date/time stamp in say column A.
I was only asking it to check to see if it was blank so that it was okay to paste the data if not move to the next cell and stamp that.
Clearly I would need to ensure that there was sufficient space in the column.
Thanks for your time.
Mike54

See if this is what you mean:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
    
    Range("A65536").End(xlUp).Offset(1, 0).Value = Now()
    
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Mike54]

Hello Peter sorry for the late reply **** meetings etc. Many thanks for taking the time and trouble to help me, I'm not quite sure why but your answer came out like this....

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>

Range("A65536").End(xlUp).Offset(1, 0).Value = Now()

Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

However I managed to take out the HTML type stuff and was left with just a line of code which works perfectly cheers it looks so simple and elegant, not that i understand VBA ranges but I'll keep working. Mike54

 

[Mike54]

Hello Peter sorry for the late reply **** meetings etc. Many thanks for taking the time and trouble to help me, I'm not quite sure why but your answer came out like this....

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>

Range("A65536").End(xlUp).Offset(1, 0).Value = Now()

Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

However I managed to take out the HTML type stuff and was left with just a line of code which works perfectly cheers it looks so simple and elegant, not that i understand VBA ranges but I'll keep working. Mike54