Using macro to find first empty cell in column. If range is empty returns error.

Hi,
I am using this macro to take me to the first empty cell in column C, run from a shape button. “Range("C208").End(xlDown).Offset(1, 0).Select” Which is working great.
My little problem is when there is not any data entered in column C it returns a Run Time Error.
All I want is a message box to say “No data entered for this month” rather than the error message
I have tried google but for some reason can not find anything that works for me.

Error is application defined or object defined error 1004

Many thanks in advance.

Sintek, Thank you for your reply. I am really sorry but I have not given you enough info for my worksheet.

I have 12 buttons and 12 different macros, 1 for each month which applies to different areas of column C. I need to look at earlier months rather than just the last entry.

Range("C4").End(xlDown).Offset(1, 0).Select
Range("C206").End(xlDown).Offset(1, 0).Select
Range("C408").End(xlDown).Offset(1, 0).Select
Range("C610").End(xlDown).Offset(1, 0).Select
Range("C812").End(xlDown).Offset(1, 0).Select
Range("C1014").End(xlDown).Offset(1, 0).Select
Range("C1216").End(xlDown).Offset(1, 0).Select
Range("C1418").End(xlDown).Offset(1, 0).Select
Range("C1620").End(xlDown).Offset(1, 0).Select
Range("C1822").End(xlDown).Offset(1, 0).Select
Range("C2024").End(xlDown).Offset(1, 0).Select
Range("C2226").End(xlDown).Offset(1, 0).Select

Marc L, Thanks for reply. I have already got named ranges in place for each month, but I don’t know how to do what you suggest!

So it depends on your logic for each name …

Hi Marc L,

The named ranges I use are,

Statement_Apr refers to =Statements!$C$4:$C$203
Statement_May refers to =Statements!$C$206:$C$405
Statement_Jun refers to =Statements!$C$408:$C$607
Statement_Jul refers to =Statements!$C$610:$C$809
Statement_Aug refers to =Statements!$C$812:$C$1011
Statement_Sep refers to =Statements!$C$1014:$C$1213
Statement_Oct refers to =Statements!$C$1216:$C$1415
Statement_Nov refers to =Statements!$C$1418:$C$1617
Statement_Dec refers to =Statements!$C$1620:$C$1819
Statement_Jan refers to =Statements!$C$1822:$C$2021
Statement_Feb refers to =Statements!$C$2024:$C$2223
Statement_Mar refers to =Statements!$C$2226:$C$2425

It's time you attach a workbook, it should be easier for any helper …

Please see attached workbook

Please Login or Register  to view this content.

Would it not be more convenient and easier to maintain if each month had it's own sheet? (Just thinking out loud here)

Originally Posted by A440

Please see attached workbook

With your smart worksheet, you just needs two procedures for all the buttons ! (except Home)
Remove previous code (except the Home procedure), paste next code
then affect each procedure to corresponding buttons and enjoy !

Paste these procedures to the Sheet36(Statements) worksheet module :

PHP Code: 

Sub FirstBlank()
        V = Application.Caller:  If IsError(V) Then Exit Sub
    With Range("Statement_" & Me.Shapes(V).TextFrame.Characters.Text)
        If IsEmpty(.Cells(1)) Then .Cells(1).Select Else .Cells(.Count)(2).End(xlUp)(2).Select
    End With
End Sub


Sub NameRange()
    V = Application.Caller:  If IsError(V) Then Exit Sub
    Application.Goto Range("Statement_" & Me.Shapes(V).TextFrame.Characters.Text), True
End Sub 


Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

jolivanes, Thanks for the suggestion, the worksheet is a copy from my main file which has to many sheets in it already lol

sktneer, Thank you for your reply. Great solution, I have ideas for using this else where as well.

You are lucky as sktneer solution works only on computers with english language, not for my local language for example …

Marc L, Thank you very much for your code, now using it in my worksheet. I really wish I could take time out to learn vba properly,but hey thankfully there are forums like this to help us out.

You're welcome and thanks for the rep' !