CODEWITHSUNDEEP
vbaexcel
Dan
Dan

Reputation: 1033

vba: variable not defined in activeCell

I'm new to vba so am learning. I'm struggling to understand why this is failing in Excel 2016 and my research is drawing a blank. I have the following code

Option Explicit

Sub Range_End_Method()

Dim lRow As Long
Dim lCol As Long
Dim i As Integer
Dim rng As Range

Set rng = ActiveCell

lRow = Cells(Rows.Count, 1).End(xlUp).Row

lCol = Cells(1, Columns.Count).End(xlToLeft).Column

    Do While rng.Value <> Empty
        If InStr(rng.Value, "Petrol") = 0 Then
            currentRow = ActiveCell.Row
            Worksheets("MySheet").Cells(currentRow, 5) = "Shopping"
            Set rng = rng.Offset(1)
            rng.Select
        Else
            currentRow = ActiveCell.Row
            Worksheets("MySheet").Cells(currentRow, 9) = "Not Found"
            Set rng = rng.Offset(1)
            rng.Select
        End If
   Loop

End Sub

If I open the vba editor and type this into a "Microsoft Excel Objects" sheet I don't get an error. But if I run as a "Modules" naming my module as "m_End" I get an error thrown up saying

Compile Error

Variable not defined

The variable it highlights in vba editor is "currentRow" from the line in the first "If condition":

currentRow = ActiveCell.Row

I don't understand what the difference is and how to fix the error.

Any help would be appreciated.

Upvotes: 2

Views: 1228

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

Since you posted the rest of your code, that error is clearly because you did not declare currentRow in your Dim statements. Possibly option Explicit was not in your sheet module, so no error.

In any event, go to Tools ► Options ► Editor ► Code Settings and Select Require Variable Declaration It will save you loads of debugging time in the future. Perhaps in your sheet module you did not have Option Explicit

I would declare it as a Long

Oh, and in VBA there is no advantage (and there are some disadvantages) to declaring a data type as Integer. They get converted to Longs internally anyway; and the values which can be assigned are limited. Check VBA specifications for data types for the various limits.

Upvotes: 2

Related Questions