emptyRow code giving Compile Error "Variable not defined"

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:

Dim ws As Worksheet
Set ws = Worksheets(“Sheet1”)

emptyRow = WorksheetFunction.CountA(Range(“B:B”)) + 5

ws.Cells(emptyRow, 2).Value = TextBox1.Value
ws.Cells(emptyRow, 3).Value = TextBox2.Value
ws.Cells(emptyRow, 4).Value = TextBox3.Value

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

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"))

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”

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