"variable not defined" error with "sub" highlighted

[jalberthenet]

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]

Welcome to the Forum!

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

 

[Michael M]

MAybe this

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]

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]

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]

@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]

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:

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