emptyRow code giving Compile Error "Variable not defined"

[colvinb]

I am very new to VBA and occurred a problem that I recently received some great advice from an excel Forum user, unfortunately it has thrown up another problem that I cannot seem to get around!

(I have posted a new thread rather than keep pestering my initial helper as it is not specific to my first posts title and would not help others if google-ing a similar problem.)

My problem has come because I have now included ‘option explicit’ at the top of my code so I can debug in the IDA, which is helping me greatly define all my variables etc.
I have a user form that once completed is submitted via a submit button and puts all the values on the user form into a sheet on my excel workbook. My code to submit to the correct empty rows is:

Please Login or Register  to view this content.

When I debug now it highlights the ‘emptyRow’ in the: emptyRow = WorksheetFunctions.CountA(Range(“B:B”)) + 5 line and gives the complie error: “Variable not defined”


Can anyone help please…

Many thanks

[JapanDave]

Try this,

Please Login or Register  to view this content.

[colvinb]

Hi JapanDave
Thanks for the reply,
When I put this code into my sub it says Compile Error: Expected list separator or ) while highlighting the : between the (Range("B:B"))

[colvinb]

Sorry JapanDave, when copying and pasting from your post I received that compile error but when I simply inserted .Application to my original line of code it did not produce the error but when I run the debugger again it reverts to my original problem of highlighting the ‘emptyRow’ in the: emptyRow = Application.WorksheetFunctions.CountA(Range(“B:B”)) + 5 line and gives the complie error: “Variable not defined”

[JapanDave]

In that case try this.

Are you using option explicit?

Please Login or Register  to view this content.

And if you are trying to find the first empty cell in row I would use this.

Please Login or Register  to view this content.

[JosephP]

Please Login or Register  to view this content.

since it matches the existing dim statements and type declaration characters are, imo, a mite old fashioned

[colvinb]

Thank you very much JapanDave, i've applied the changes and works perfectly now