FormulaCell throwing Variable not Defined error?

[koreyjames]

Hi All,

I have the below code which is failing and I can't for the life of me figure out why. Googling of the error messages produces vague solutions to the error, however I don't see how they're applicable to my code.

The code is placed on 'Sheet1' and references a Macro placed in a Module.

Here's the code:

Option Explicit


Private Sub Worksheet_Calculate()
    Dim FormulaRange As Range
    Dim NotSentMsg As String
    Dim MyMsg As String
    Dim SentMsg As String
    Dim MyLimit As Single


    NotSentMsg = "Not Sent"
    SentMsg = "Sent"


    'Above the MyLimit value it will run the macro
    MyLimit = 0


    'Set the range with the Formula that you want to check
    Set FormulaRange = Me.Range("Q2:Q70")


    On Error GoTo EndMacro:
    For Each FormulaCell In FormulaRange.Cells
        With FormulaCell
            If IsNumeric(.Value) = False Then
                MyMsg = "Not numeric"
            Else
                If .Value > MyLimit Then
                    MyMsg = SentMsg
                    If .Offset(0, 1).Value = NotSentMsg Then
                        Call Mail_with_outlook1
                    End If
                Else
                    MyMsg = NotSentMsg
                End If
            End If
            Application.EnableEvents = False
            .Offset(0, 1).Value = MyMsg
            Application.EnableEvents = True
        End With
    Next FormulaCell


ExitMacro:
    Exit Sub


EndMacro:
    Application.EnableEvents = True


    MsgBox "Some Error occurred." _
         & vbLf & Err.Number _
         & vbLf & Err.Description


End Sub

Any assistance that anyone could provide would be great!

Cheers,
Corey

 

[MARK858]

Try adding the line below to your Dim statements.

Dim FormulaCell As Range

 

[Trebor76]

Hi Corey,

Using Option Explicit like you're correctly doing (if you don't any undeclared variables will be assigned as variants - the most expensive type of variable in programming) means that all variables have to be defined at run time. As you haven't defined FormulaCell it's throwing an error. Try adding this variable to the list of five variables you already have:

Dim FormulaCell As Range

Regards,

Robert

 

[koreyjames]

Try adding the line below to your Dim statements.

Dim FormulaCell As Range

Mate, thank you! Worked like a charm!

 

[koreyjames]

Trebor76, thanks mate! Didn't realise that I'd need to declare a single cell search but that's magic, appreciate it!