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