"variable not defined" error with "sub" highlighted

jalberthenet

New Member
Joined
Jul 6, 2015
Messages
2
Hi all,

I have a loop code as per below, which works fine in the original spreadsheet for which it was written. Although the row containing "Sub Matrix2DB()" is highlighted yellow.
BUT when copying to a new spreadsheet, with all of the sheets, columns and rows set out as required in the macro, the error message "compile error: variable not defined" is returned. Again, the row containing "Sub Matrix2DB()" is highlighted yellow. It looks like my sub needs to be declared as a variable but I don't know how to do this, how do I fix this one please ?

Option Explicit
Dim rx, cx, cxDeliverable, cxLoc, rxCode, rz, czDeliverable, czLoc, czCode, czSubtotal As Integer
Dim shx, shz As Worksheet

Sub Matrix2DB()

Set shx = Sheet7
Set shz = Sheet9

cxDeliverable = 1
cxLoc = 2
rxCode = 7
rz = 1
czDeliverable = 1
czLoc = 2
czCode = 3
czSubtotal = 4

For rx = 9 To 613
For cx = 3 To 54
If shx.Cells(rx, cx) > 0 Then
shz.Cells(rz, czDeliverable) = shx.Cells(rx, cxDeliverable)
shz.Cells(rz, czLoc) = shx.Cells(rx, cxLoc)
shz.Cells(rz, czCode) = shx.Cells(rxCode, cx)
shz.Cells(rz, czSubtotal) = shx.Cells(rx, cx)
rz = rz + 1
End If
Next cx
Next rx

End Sub
 

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,554
Welcome to the Forum!

Does you new workbook have sheets with codenames Sheet7 and Sheet9?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,167
Office Version
2013
Platform
Windows
MAybe this
Rich (BB code):
Option Explicit
Dim rx, cx, cxDeliverable, cxLoc, rxCode, rz, czDeliverable, czLoc, czCode, czSubtotal As Integer
Dim shx, shz As Worksheet

Sub Matrix2DB()

Set shx = Sheets("Sheet7")
Set shz = Sheets("Sheet9")

cxDeliverable = 1
cxLoc = 2
rxCode = 7
rz = 1
czDeliverable = 1
czLoc = 2
czCode = 3
czSubtotal = 4

For rx = 9 To 613
For cx = 3 To 54
If shx.Cells(rx, cx) > 0 Then
shz.Cells(rz, czDeliverable) = shx.Cells(rx, cxDeliverable)
shz.Cells(rz, czLoc) = shx.Cells(rx, cxLoc)
shz.Cells(rz, czCode) = shx.Cells(rxCode, cx)
shz.Cells(rz, czSubtotal) = shx.Cells(rx, cx)
rz = rz + 1
End If
Next cx
Next rx

End Sub
 

jalberthenet

New Member
Joined
Jul 6, 2015
Messages
2
sweet jesus it looks like that worked. thankyou stephencrump and Michael m.

I renamed the relevant sheets to "sheet 7" and "sheet 9" as suggested, saved, re-opened and re-ran the macro and she seems to be up and running.

May I ask if anyone knows why it initially suggested my "sub" needed to be declared as a variable ???
 

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,554
Because you have Option Explicit (good coding practice!) execution won't proceed with an undefined variable.

So execution stops at the first line Sub Matrix2DB() which will show yellow.

However, the first offending undefined variable should also be highlighted?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,483
Office Version
365, 2010
Platform
Windows, Mobile
@jalberthenet, you might also want to review how you declare your variables.

As you have it currently written

rx, cx, cxDeliverable, cxLoc, rxCode, rz, czDeliverable, czLoc, czCode and shx are all declared as Variant in VBA, which I don't think is what you intended.

In VBA if you omit the As Type clause in a variable declaration then it is declared as Variant, Variant is the default type.
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,467
Ah MARK858 has beaten me to the punch, but just to add,

The way you're declaring your variables only the last one is being declared as the type you define as an integer or worksheet. To see this run this simple code which shows the variable type (
click here for a list of variable types and their associated number) from this:

Code:
MsgBox VarType(rx)
MsgBox TypeName(czSubtotal)
rx is actually a variant variable - the most expensive type of variable. You need "As Integer" after each of the other nine variables in that row (assuming you want them all to be integers).

Regards,

Robert
 

Forum statistics

Threads
1,081,832
Messages
5,361,586
Members
400,639
Latest member
fleyd

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