How to troubleshoot undefined variable when everything defined

wiseone

Board Regular
Joined
Mar 14, 2015
Messages
121
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.....

Code:
    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
 
Last edited:

V_Malkoti

Well-known Member
Joined
Jun 10, 2015
Messages
898
I believe it should be xlValues, not x1Values (lowercase L instead of 1). Same goes for next argument xlWhole.
 
Last edited:

wiseone

Board Regular
Joined
Mar 14, 2015
Messages
121
good god! LOL nice find....sharp eyes, I never would have seen that. Last time I write code instead of copy pasting.
 

wiseone

Board Regular
Joined
Mar 14, 2015
Messages
121
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

Well-known Member
Joined
Jun 10, 2015
Messages
898
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.

Code:
IE_Data_SKU_Count = Selection.Count
 
Last edited:

wiseone

Board Regular
Joined
Mar 14, 2015
Messages
121
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

Well-known Member
Joined
Jun 10, 2015
Messages
898
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:

Code:
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.

Code:
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.
 
Last edited:

Forum statistics

Threads
1,082,143
Messages
5,363,378
Members
400,732
Latest member
robcooper2001

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top