How to troubleshoot undefined variable when everything defined

[wiseone]

Hi,

I just started VBA this year and am struggling to troubleshoot...hoping you can help.

I have some code I upgraded today which filters a table and copies the first column to another sheet.

It uses a dynamic named range as the header to find the right column to filter (new today).
It uses a dynamic named range to define the table to filer (new today).

Both of these are to allow for insertion/deletion of columns over time.

It also checks the second sheet for the number of entries and adds or deletes rows because there is additional data below the copy/paste dataset. (New today).

For some reason, I am now getting "Compile Error: Variable not defined" and it goes to "x1Values" on the "Set Range2 row". Option explicit is enabled and all variables are defined ahead of time.

Any suggestions on why I'm getting this error, or how to troubleshoot?

Google was hopeless.....searched for an hour.

Thanks in advance!

Oh, there is more code above and below this section...it all works perfectly when I comment out this section....not sure its relevant or not.....

    Dim Col_num1 As Integer
    Dim Col_num2 As Integer
    Dim Range1 As Range
    Dim Range2 As Range
    Dim Weber_SKU_Count As Integer
    Dim IE_Data_SKU_Count As Integer
    Dim counter As Integer


    Sheets("IE_Download").Select


    With ActiveSheet     'Checks if sheet filters are all cleared.  If yes, then do nothing.  Prevents error from popping up.
        If .FilterMode Then
            .ShowAllData
        End If
    End With


    Set Range1 = ActiveSheet.Range("datamatch").Find("Category", , xlValues, xlWhole)
    Set Range2 = ActiveSheet.Range("datamatch").Find("Slicing Hall", , x1Values, x1Whole)


    Col_num1 = Range1.Column
    Col_num2 = Range2.Column


    'datamatch and dataall are dynamic named ranges which are configured in the name manager.
    'datamatch = the header in the IE_download.
    'dataall = the header and the data, matched to the width of datamatch.


    'Filter to correct list
    ActiveSheet.Range("dataall").AutoFilter Field:=Col_num2, Criteria1:=Array( _
        "10", "11", "12", "13", "4", "5", "6", "7A", "8", "9", "="), Operator:=xlFilterValues
    ActiveSheet.Range("dataall").AutoFilter Field:=Col_num1, Criteria1:=Array( _
        "BACON", "BOLOGNA", "SLICED MEATS"), Operator:=xlFilterValues
    Range("A10").Select
    Range(Selection, Selection.End(xlDown)).Select


    'Count number of skus filtered
    Set IE_Data_SKU_Count = Selection.Count


    Sheets("Weber Calculations").Select
    With ActiveSheet     'Checks if sheet filters are all cleared.  If yes, then do nothing.  Prevents error from popping up.
        If .FilterMode Then
            .ShowAllData
        End If
    End With


    Range("A3").Select
    Range(Selection, Selection.End(xlDown)).Select


    'Count number of skus on weber calculator
    Set Weber_SKU_Count = Selection.Count


    'Insert or delete number of rows needed so overlap does not occur. - Maintains existing blank rows between regular skus and PDP skus.
    If IE_Data_SKU_Count > Weber_SKU_Count Then
        For counter = 1 To IE_Data_SKU_Count - Weber_SKU_Count
            Rows("Weber_SKU_Count+4").Select
            Selection.Insert Shift:=xlDown
        Next i
    If IE_Data_SKU_Count < Weber_SKU_Count Then
        For counter = 1 To Weber_SKU_Count - IE_Data_SKU_Count
            Rows("Weber_SKU_Count+4").Select
            Selection.Delete Shift:=xlUp
        Next i


    'Clear old sku list on weber tab to ensure eliminating duplicate skus at bottom of list.
    Range("A3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents


    'Copy and paste from IE_download to Weber
    Sheets("IE_Download").Select
    Range("A10").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Weber Calculations").Select
    Range("A2").Select
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 

[V_Malkoti]

I believe it should be xlValues, not x1Values (lowercase L instead of 1). Same goes for next argument xlWhole.

 

[wiseone]

good god! LOL nice find....sharp eyes, I never would have seen that. Last time I write code instead of copy pasting.

 

[wiseone]

now getting another error

"Compile error: Object required"

and it highlights:

Set IE_Data_SKU_Count = Selection.Count

Thoughts on whats causing this error?

Is there any reference data on troubleshooting errors?

 

[V_Malkoti]

The variable IE_Data_SKU_Count is declared as an integer but the statement is trying to set its value as an object. Remove the set keyword and assignment should work - Assignment to primitive data types (integer, long, double, boolean, etc) does not require Set keyword.

IE_Data_SKU_Count = Selection.Count

 

[wiseone]

Thanks again
Sorry for so many questions. But got couple more.
When should "set" be used and not used. What is the difference between an object and other defined variables?

 

[V_Malkoti]

When dealing with primitive values, you don't need Set to assign value to variable. Primitive values are simple values such as numbers, text or boolean. Here is an example:

Dim a as Integer
Dim b as Double
Dim c as String
Dim d as Boolean
a = 100
b = 32.82
c = "Test"
d = True

Here is the list of data types supported : https://msdn.microsoft.com/en-us/library/office/gg251528.aspx

Set keyword is used when assigning object to an object variable. Objects are like entities that have many properties, each with its own value. An object can also have a number of methods associated with it. These attributes and methods can be invoked on that object using dot operator. For example, in following lines of code we declare a variable of type Worksheet (so that it can be used to store reference to a Worksheet object), and assign YearlyData sheet to it. Then we access its name and visiblity properties and set a password to it with Protect method of the object.

Dim sh as Worksheet
Set sh = ActiveWorkbook.Sheets("YearlyData")
sh.Name
sh.Visible
sh.Protect "mypassword"

Ability to debug errors comes with familiarity of VBA coding syntax and object model of the libraries used by application.
You can get started with following article : https://msdn.microsoft.com/en-us/library/office/ee814737(v=office.14).aspx
Excel object model reference: https://msdn.microsoft.com/en-us/library/office/ff194068.aspx

and follow up with any VBA book that you like.
And if you have more questions or have a problem in your MS-Excel code, feel free to post here on MrExcel.

 

[wiseone]

Great thank you!