Incremental Numbering: Compile Error - Variable not defined

[BEEJAY]

Using Excel 2003
After weeks of researching and studying my books and various groups, I
decided I had to start from scratch so I could understand what was happening.
The following is my feeble attempt, so far. I'm getting stuck on the above
problem.
Even after going thru help files, etc., I can't determine exactly what I'm
missing or over-looking. My various books, etc., advise that it is wise to
start my code with "option Explicit". I think that is what is now stopping me
in my tracks.
If someone would be willing to work the code thru with me, it would be
greatly appreciated.
The Warning comes up on code line 7, =x1Values,

Option Explicit

' The VB Code is in Module 11 of the "XXXXX" Menu Add-In.
' Upon completion of project, the SeqNum will be actived from this Add-In
Menu.
' The Menu Sub-Item will read: Insert QUOTE #.

' Contract_Page1 is representative of Sheet #1 of each of the 12 workbooks
' that the Saleman works with.
' The Contract Number is to be put INTO Cell C5 of Sheet #1 of each/any
' of the Contracts - Whichever is being worked on at the time

' The "QCNUM.XLS", the Number Master Workbook, will be sent to each saleman
' to be installed in exactly the same path, on each computer, namely:
' C:\Excel Add_Ins\QCNUM.xls

Sub SeqNum()

'Open Workbook C:\Excel Add_Ins\QCNUM.XLS
Workbooks("C:\Excel Add_Ins\QCNUM.XLS").Worksheets("Sheet1").Activate

'Copy Cell H6 of QCNUM.xls
Range("F6").Select
Selection.Copy

'Paste into Open Contract Worksheet, Cell C5
'(The activeworkbook is the book that calls in the Add-In Workbook
' but under current conditions, I don't think this qualifies as being
' the active workbook)

ActiveWorkbook.Activate
Range("C5").Select

'PROBLEM LINE IS NEXT:
Selection.PasteSpecial Paste:=x1Values, Operation:=x1None, _
SkipBlanks:=False, Transpose:=False

ActiveWorkbook.Save

GoTo Workbooks("C:\Excel Add_Ins\QCNUM.XLS").Worksheets("Sheet1")

'Copy Cell H4 of QCNUM.xls - Paste to Cell H3 of QCNUM.xls
'This copies the most recent used number into the "starting number" cell,
'which then makes cell H4 increment by 1

Range("F4").Select
Selection.Copy
Range("F3").Select
Selection.PasteSpecial Paste:=x1Values, Operation:=x1None, _
SkipBlanks:=False, Transpose:=False

'Save QCNUM.xls
FileSave ("C:\Excel Add_Ins\QCNUM.xls")

' Exit QCNUM.xls
Close ("C:\Excel Add_Ins\QCNUM.xls")

End Sub

 

[Robin Hammond]

Beejay,

It looks like you are using a "1" rather than a "l" character.

Here's what is should be:
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Your original:

Selection.PasteSpecial Paste:=x1Values, Operation:=x1None, _
SkipBlanks:=False, Transpose:=False

Initially, if you didn't have optionexplicit set, VBA will probably have
interpreted an undeclared variable (x1values) as a zero.

Robin Hammond
www.enhanceddatasystems.com

 

[Dave Peterson]

That "Option Explicit" is a very good thing.

This will force you to declare any variables that you use. Without that, your
code may have run, but may not have done what you wanted. (Undeclared variables
could be treated as 0, "", ...)

And it'll help you find typos:
xlvalues is EX-ELL-values
You have EX-One-values

and EX-ELL-None, not EX-one-None, too.

But you have other trouble, too:

Once a workbook is open, you don't specify the path:
Workbooks("C:\Excel Add_Ins\QCNUM.XLS")
becomes
Workbooks("QCNUM.XLS")

FileSave doesn't exist.
and same with the way you used Close.

And I'm not quite sure what the second portion of your code does.

'Copy Cell H4 of QCNUM.xls - Paste to Cell H3 of QCNUM.xls

But you copy a cell from a worksheet in QCNUM.xls--and paste to a worksheet,
too.

I'm not sure what should happen with that portion.

And in general, you don't have to select ranges to work with them.

This may not do exactly what you want, but it may give you an idea:

Option Explicit
Sub SeqNum()

Dim RngToCopy As Range
Dim DestCell As Range

With Workbooks("QCNUM.XLS").Worksheets("Sheet1")
Set RngToCopy = .Range("f6")
End With

With Workbooks("qcnum.xls").Worksheets("Open Contract")
Set DestCell = .Range("c5")
End With

RngToCopy.Copy
DestCell.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


'Save and close QCNUM.xls
With Workbooks("QCNUM.xls")
.Save
.Close savechanges:=False
End With

End Sub

 

[BEEJAY]

Thanks Both for the correction regarding "1" and "l" (ell)

I have tried to use your sample, Dave, and I feel that I am SO close.
My newest coding requires TWO RngToCopy and DestCell
- Each set references different from and destination cells.
What can I do to get around that?

Further, it just dawned on me that it is possible that there may be more
than one Contract workbook open at time - Even though likely only one will be
the "active" book. However, if the others are open (behind or minimized), I
can't safetly reference Workbooks(1), since the one that needs the Quote
Number might not have been the first workbook opened.
I prefer to trigger the Quote Number instruction from the Special Menu, but
I can't see how I can direct the pasting of the number into the correct
Workbook, IF more than one (of the 12) is open, or ANY WorkBook.
As I see it, even if I put the VB with the Contract Page of each of the 12
Workbooks, I still wouldn't be able to trigger the Quote Number instruction
from the menu, correct?
Does that mean I have to put a Button or Click Event (whatever that is) on
cell C5 of each Contract Sheet to trigger the numbering process?

Thanks again for your help so far.

 

[Dave Peterson]

You could just use the two variables. Set them once, do the copy. Change them
and do the second copy.

I'm confused about what's going on in the rest of your post and what you're
really trying to do.

Maybe one more description attempt using the workbook names and worksheet names
as well as the addresses of the cells would be useful.

 

[BEEJAY]

I just finished my last week of holidays for the year, hence the delay in my
response to this post.

It has become clear to me that there is a potential large problem at the
start of my "procedure". Just so I don't further compound any confusion I
have caused so far, I'd like to see if we can clear this up first.

Lets say salesman has 3 work-books open.
Book A is his .................
Book B is his ...................
Book C is the Quote he is finishing for customer.

The Quote is completed.
Salesman selects INSERT QUOTE NUMBER from Special Menu
(Called"XMENU", which I have supplied him with)
(in order to allow the salesman to control when and where a quote
number
is required, I don't want to "automate" it at file open, or
file print)
The VB instructions in module 1-1 of "XMENU" opens a file called QCNUM.xls.
QCNUM.xls is the file where the Quote number is created, and copied from.
(I presume this is now the ActiveWorkBook.)

The Quote number is to be copied from QCNUM.xls (Sheet 1, Cell H6) and to be
pasted into Book C, Sheet 1 called "Contract", into Cell C5.

My Question: What (if any) instruction can I use that will make Book C
the active workbook again, in order to be able to paste the quote number into
Book C (and NOT into Book A or Book B. Since there are currently 12 different
quotation "masters", the original file name cannot be specified within the VB
instructions.

I first thought I could use "ActiveWorkBook", but since Book C is NOT the
calling workbook I expect that won't work.

For purposes of design and maintenance, I thought the best way to set up the
procedure would be to "attach" the instructions in ONE location (the
"XMENU"), rather than with each quotation master.

I hope I haven't muddied the waters further.

 

[Dave Peterson]

If you open the workbooks in code, you could use:

dim WkbkA as workbook
dim wkbkB as workbook
dim wkbkC as workbook

set wkbkA = workbooks.open(filename:="C:\a.xls")
set wkbkb = workbooks.open(filename:="C:\b.xls")
set wkbkc = workbooks.open(filename:="C:\c.xls")

Then refer to the workbooks by the variable that refers to them.

WkbkA.worksheets(1).range("a1").value = "hi there"

===
If your workbook with the code is the one that's important, you can refer to it
via:

Thisworkbook.worksheets(1).range("a1").value = "ok"

But I think the waters are pretty muddy.

========
Another way might be to have your macro open the workbook that needs to be
modified. You can get the filename from the user and do all the work:

dim myFileName as variant
dim wkbk as workbook

myfilename = application.getopenfilename("Excel files, *.xls")
if myfilename = false then
'user hit cancel
exit sub
end if

set wkbk = workbooks.open(filename:=myfilename)
'do all the work against the opened workbook.
wkbk.worksheets(1).range("a1") = "done"
wkbk.save
wkbk.close savechanges:=false

=======
Just to add some more mud!

I just finished my last week of holidays for the year, hence the delay in my
response to this post.

It has become clear to me that there is a potential large problem at the
start of my "procedure". Just so I don't further compound any confusion I
have caused so far, I'd like to see if we can clear this up first.

Lets say salesman has 3 work-books open.
Book A is his .................
Book B is his ...................
Book C is the Quote he is finishing for customer.

The Quote is completed.
Salesman selects INSERT QUOTE NUMBER from Special Menu
(Called"XMENU", which I have supplied him with)
(in order to allow the salesman to control when and where a quote
number
is required, I don't want to "automate" it at file open, or
file print)
The VB instructions in module 1-1 of "XMENU" opens a file called QCNUM.xls.
QCNUM.xls is the file where the Quote number is created, and copied from.
(I presume this is now the ActiveWorkBook.)

The Quote number is to be copied from QCNUM.xls (Sheet 1, Cell H6) and to be
pasted into Book C, Sheet 1 called "Contract", into Cell C5.

My Question: What (if any) instruction can I use that will make Book C
the active workbook again, in order to be able to paste the quote number into
Book C (and NOT into Book A or Book B. Since there are currently 12 different
quotation "masters", the original file name cannot be specified within the VB
instructions.

I first thought I could use "ActiveWorkBook", but since Book C is NOT the
calling workbook I expect that won't work.

For purposes of design and maintenance, I thought the best way to set up the
procedure would be to "attach" the instructions in ONE location (the
"XMENU"), rather than with each quotation master.

I hope I haven't muddied the waters further.

 

[BEEJAY]

Thanks to your stirring the mud some more, I've got another brain shock.
Let's start from scratch!!

1: Quote/Contract has been saved with special name
(unknown - Each salesman has their own idea on how to name their
files)
2: We need to open QCNUM.xls to access the numbering "system".
3: Once we are in QCNUM, we need a way to get back to the correct
workbook (wb), regardless of how many other wb's may be open at that
time.
4: My thought is:
Save the new wb name in cell A100 of the Contract (page 1)
Copy/Paste that wb name in Cell F8 of QCNUM.
NOW the question is, can the VB code use the wb name in F8 to direct the
copy/paste of the Quote number back to the the correct wb. In other words,
can VB be written to say: Go to wb specifed in Cell F8?
If yes, then we'll need to start with something like the following:

Sub SeqNum()
' Quote Number Macro
' 9-27-05: To assign quote number to Contract forms
' Keyboard Shortcut: Ctrl+Shift+Q
' Save wb name of new Contract to out of way location: Use Cell A100
' (The wb name will be different each time this procedure is used).

' THE following is already a problem. I get a compile error if the word
TOTAL
' is not at the beginning of the string. (I have no idea what that does
or means)
' As it reads now, it doesn't like the reference A100;
' COMPILE ERROR - Variable Undefined
' Also concerned if the following will accept super long filenames or will
it
' truncate at 30 characters?

Total = Mid(Cell("filename", A100), Find("[", Cell("filename", A100), 1)
+ 1, _
Find("]", Cell("filename", A100), 1) - Find("[", Cell("filename", A1),
1) - 1)

' File QCNUM.xls to be opened
' File name in A100 to be copied to C8 in QCNUM.xls

Dim RngToCopy As Range
Dim DestCell As Range

' To Copy FROM (Newly Named Quote, Cell A100)
With Workbooks(Cell A100).Worksheets("Contract")
Set RngToCopy = .Range("A100")

' To Paste INTO (QCNUM.xls must be opened first)
With Workbooks(c:\Excel Add_Ins\QCNUM.xls).Worksheets("Sheet1").Open
Set DestCell = .Range("F8")
End With

End Sub

IF this clip makes any sense to you and we can get it working, the rest of
the procedure should be relatively easy, as I see it.

 

[Dave Peterson]

I think I'd make the user start in the workbook with the unknown special name.

You could ask:

dim resp as long
dim wkbk as workbook

resp = msgbox(Prompt:="Is " & activeworkbook.fullname & " the correct book?", _
buttons:=vbyesno)

if resp = vbno then
msgbox "Please select the correct workbook and try again!
exit sub
end if

set wkbk = activeworkbook

....

wkbk.activate

==========
You could fiddle around with a hidden name in the workbook (and look for that
hidden name), but what happens if the user has two workbooks open that have the
same hidden name -- the real one and their backup (say).

Sometimes, it's just easier to ask.

If you want, you could create a userform with the list of workbook names and
have the user choose the one that they need.

Thanks to your stirring the mud some more, I've got another brain shock.
Let's start from scratch!!

1: Quote/Contract has been saved with special name
(unknown - Each salesman has their own idea on how to name their
files)
2: We need to open QCNUM.xls to access the numbering "system".
3: Once we are in QCNUM, we need a way to get back to the correct
workbook (wb), regardless of how many other wb's may be open at that
time.
4: My thought is:
Save the new wb name in cell A100 of the Contract (page 1)
Copy/Paste that wb name in Cell F8 of QCNUM.
NOW the question is, can the VB code use the wb name in F8 to direct the
copy/paste of the Quote number back to the the correct wb. In other words,
can VB be written to say: Go to wb specifed in Cell F8?
If yes, then we'll need to start with something like the following:

Sub SeqNum()
' Quote Number Macro
' 9-27-05: To assign quote number to Contract forms
' Keyboard Shortcut: Ctrl+Shift+Q
' Save wb name of new Contract to out of way location: Use Cell A100
' (The wb name will be different each time this procedure is used).

' THE following is already a problem. I get a compile error if the word
TOTAL
' is not at the beginning of the string. (I have no idea what that does
or means)
' As it reads now, it doesn't like the reference A100;
' COMPILE ERROR - Variable Undefined
' Also concerned if the following will accept super long filenames or will
it
' truncate at 30 characters?

Total = Mid(Cell("filename", A100), Find("[", Cell("filename", A100), 1)
+ 1, _
Find("]", Cell("filename", A100), 1) - Find("[", Cell("filename", A1),
1) - 1)

' File QCNUM.xls to be opened
' File name in A100 to be copied to C8 in QCNUM.xls

Dim RngToCopy As Range
Dim DestCell As Range

' To Copy FROM (Newly Named Quote, Cell A100)
With Workbooks(Cell A100).Worksheets("Contract")
Set RngToCopy = .Range("A100")

' To Paste INTO (QCNUM.xls must be opened first)
With Workbooks(c:\Excel Add_Ins\QCNUM.xls).Worksheets("Sheet1").Open
Set DestCell = .Range("F8")
End With

End Sub

IF this clip makes any sense to you and we can get it working, the rest of
the procedure should be relatively easy, as I see it.

 

[BEEJAY]

Dave, thanks for your help so far.
I think I've made a big mess of this post.
Every time I learn something new, I have to rethink my layout, which
in turn changes cell references, etc.
I think we best consider this post Done.
I'll re-think and start a new post based on my new found knowledge.
I'll try to make the next post clearer and more concise.
Thanks again for all your input.

I think I'd make the user start in the workbook with the unknown special name.

You could ask:

dim resp as long
dim wkbk as workbook

resp = msgbox(Prompt:="Is " & activeworkbook.fullname & " the correct book?", _
buttons:=vbyesno)

if resp = vbno then
msgbox "Please select the correct workbook and try again!
exit sub
end if

set wkbk = activeworkbook

....

wkbk.activate

==========
You could fiddle around with a hidden name in the workbook (and look for that
hidden name), but what happens if the user has two workbooks open that have the
same hidden name -- the real one and their backup (say).

Sometimes, it's just easier to ask.

If you want, you could create a userform with the list of workbook names and
have the user choose the one that they need.

Thanks to your stirring the mud some more, I've got another brain shock.
Let's start from scratch!!

1: Quote/Contract has been saved with special name
(unknown - Each salesman has their own idea on how to name their
files)
2: We need to open QCNUM.xls to access the numbering "system".
3: Once we are in QCNUM, we need a way to get back to the correct
workbook (wb), regardless of how many other wb's may be open at that
time.
4: My thought is:
Save the new wb name in cell A100 of the Contract (page 1)
Copy/Paste that wb name in Cell F8 of QCNUM.
NOW the question is, can the VB code use the wb name in F8 to direct the
copy/paste of the Quote number back to the the correct wb. In other words,
can VB be written to say: Go to wb specifed in Cell F8?
If yes, then we'll need to start with something like the following:

Sub SeqNum()
' Quote Number Macro
' 9-27-05: To assign quote number to Contract forms
' Keyboard Shortcut: Ctrl+Shift+Q
' Save wb name of new Contract to out of way location: Use Cell A100
' (The wb name will be different each time this procedure is used).

' THE following is already a problem. I get a compile error if the word
TOTAL
' is not at the beginning of the string. (I have no idea what that does
or means)
' As it reads now, it doesn't like the reference A100;
' COMPILE ERROR - Variable Undefined
' Also concerned if the following will accept super long filenames or will
it
' truncate at 30 characters?

Total = Mid(Cell("filename", A100), Find("[", Cell("filename", A100), 1)
+ 1, _
Find("]", Cell("filename", A100), 1) - Find("[", Cell("filename", A1),
1) - 1)

' File QCNUM.xls to be opened
' File name in A100 to be copied to C8 in QCNUM.xls

Dim RngToCopy As Range
Dim DestCell As Range

' To Copy FROM (Newly Named Quote, Cell A100)
With Workbooks(Cell A100).Worksheets("Contract")
Set RngToCopy = .Range("A100")

' To Paste INTO (QCNUM.xls must be opened first)
With Workbooks(c:\Excel Add_Ins\QCNUM.xls).Worksheets("Sheet1").Open
Set DestCell = .Range("F8")
End With

End Sub

IF this clip makes any sense to you and we can get it working, the rest of
the procedure should be relatively easy, as I see it.

If you open the workbooks in code, you could use:

dim WkbkA as workbook
dim wkbkB as workbook
dim wkbkC as workbook

set wkbkA = workbooks.open(filename:="C:\a.xls")
set wkbkb = workbooks.open(filename:="C:\b.xls")
set wkbkc = workbooks.open(filename:="C:\c.xls")

Then refer to the workbooks by the variable that refers to them.

WkbkA.worksheets(1).range("a1").value = "hi there"

===
If your workbook with the code is the one that's important, you can refer to it
via:

Thisworkbook.worksheets(1).range("a1").value = "ok"

But I think the waters are pretty muddy.

========
Another way might be to have your macro open the workbook that needs to be
modified. You can get the filename from the user and do all the work:

dim myFileName as variant
dim wkbk as workbook

myfilename = application.getopenfilename("Excel files, *.xls")
if myfilename = false then
'user hit cancel
exit sub
end if

set wkbk = workbooks.open(filename:=myfilename)
'do all the work against the opened workbook.
wkbk.worksheets(1).range("a1") = "done"
wkbk.save
wkbk.close savechanges:=false

=======
Just to add some more mud!



BEEJAY wrote:

I just finished my last week of holidays for the year, hence the delay in my
response to this post.

It has become clear to me that there is a potential large problem at the
start of my "procedure". Just so I don't further compound any confusion I
have caused so far, I'd like to see if we can clear this up first.

Lets say salesman has 3 work-books open.
Book A is his .................
Book B is his ...................
Book C is the Quote he is finishing for customer.

The Quote is completed.
Salesman selects INSERT QUOTE NUMBER from Special Menu
(Called"XMENU", which I have supplied him with)
(in order to allow the salesman to control when and where a quote
number
is required, I don't want to "automate" it at file open, or
file print)
The VB instructions in module 1-1 of "XMENU" opens a file called QCNUM.xls.
QCNUM.xls is the file where the Quote number is created, and copied from.
(I presume this is now the ActiveWorkBook.)

The Quote number is to be copied from QCNUM.xls (Sheet 1, Cell H6) and to be
pasted into Book C, Sheet 1 called "Contract", into Cell C5.

My Question: What (if any) instruction can I use that will make Book C
the active workbook again, in order to be able to paste the quote number into
Book C (and NOT into Book A or Book B. Since there are currently 12 different
quotation "masters", the original file name cannot be specified within the VB
instructions.

I first thought I could use "ActiveWorkBook", but since Book C is NOT the
calling workbook I expect that won't work.

For purposes of design and maintenance, I thought the best way to set up the
procedure would be to "attach" the instructions in ONE location (the
"XMENU"), rather than with each quotation master.

I hope I haven't muddied the waters further.

:

You could just use the two variables. Set them once, do the copy. Change them
and do the second copy.

I'm confused about what's going on in the rest of your post and what you're
really trying to do.

Maybe one more description attempt using the workbook names and worksheet names
as well as the addresses of the cells would be useful.

BEEJAY wrote:

Thanks Both for the correction regarding "1" and "l" (ell)

I have tried to use your sample, Dave, and I feel that I am SO close.
My newest coding requires TWO RngToCopy and DestCell
- Each set references different from and destination cells.
What can I do to get around that?

Further, it just dawned on me that it is possible that there may be more
than one Contract workbook open at time - Even though likely only one will be
the "active" book. However, if the others are open (behind or minimized), I
can't safetly reference Workbooks(1), since the one that needs the Quote
Number might not have been the first workbook opened.
I prefer to trigger the Quote Number instruction from the Special Menu, but
I can't see how I can direct the pasting of the number into the correct
Workbook, IF more than one (of the 12) is open, or ANY WorkBook.
As I see it, even if I put the VB with the Contract Page of each of the 12
Workbooks, I still wouldn't be able to trigger the Quote Number instruction
from the menu, correct?
Does that mean I have to put a Button or Click Event (whatever that is) on
cell C5 of each Contract Sheet to trigger the numbering process?

Thanks again for your help so far.

:

That "Option Explicit" is a very good thing.

This will force you to declare any variables that you use. Without that, your
code may have run, but may not have done what you wanted. (Undeclared variables
could be treated as 0, "", ...)

And it'll help you find typos:
xlvalues is EX-ELL-values
You have EX-One-values

and EX-ELL-None, not EX-one-None, too.

But you have other trouble, too:

Once a workbook is open, you don't specify the path:
Workbooks("C:\Excel Add_Ins\QCNUM.XLS")
becomes
Workbooks("QCNUM.XLS")

FileSave doesn't exist.
and same with the way you used Close.

And I'm not quite sure what the second portion of your code does.

'Copy Cell H4 of QCNUM.xls - Paste to Cell H3 of QCNUM.xls

But you copy a cell from a worksheet in QCNUM.xls--and paste to a worksheet,
too.

I'm not sure what should happen with that portion.

And in general, you don't have to select ranges to work with them.

This may not do exactly what you want, but it may give you an idea:

Option Explicit
Sub SeqNum()

Dim RngToCopy As Range
Dim DestCell As Range

With Workbooks("QCNUM.XLS").Worksheets("Sheet1")
Set RngToCopy = .Range("f6")
End With

With Workbooks("qcnum.xls").Worksheets("Open Contract")
Set DestCell = .Range("c5")
End With

RngToCopy.Copy
DestCell.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


'Save and close QCNUM.xls
With Workbooks("QCNUM.xls")
.Save
.Close savechanges:=False
End With

End Sub

BEEJAY wrote:

Using Excel 2003
After weeks of researching and studying my books and various groups, I
decided I had to start from scratch so I could understand what was happening.
The following is my feeble attempt, so far. I'm getting stuck on the above
problem.
Even after going thru help files, etc., I can't determine exactly what I'm
missing or over-looking. My various books, etc., advise that it is wise to
start my code with "option Explicit". I think that is what is now stopping me
in my tracks.
If someone would be willing to work the code thru with me, it would be
greatly appreciated.
The Warning comes up on code line 7, =x1Values,

Option Explicit