Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incremental Numbering: Compile Error - Variable not defined
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incremental Numbering: Compile Error - Variable not defined
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 "BEEJAY" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incremental Numbering: Compile Error - Variable not defined
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 ' 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incremental Numbering: Compile Error - Variable not defined
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" wrote: 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 ' 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incremental Numbering: Compile Error - Variable not defined
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. "Dave Peterson" wrote: 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 ' 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 -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incremental Numbering: Compile Error - Variable not defined
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" wrote: 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. "Dave Peterson" wrote: 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 ' 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 -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incremental Numbering: Compile Error - Variable not defined
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. "Dave Peterson" wrote: 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. "Dave Peterson" wrote: 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 ' 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incremental Numbering: Compile Error - Variable not defined
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" wrote: 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. "Dave Peterson" wrote: 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. "Dave Peterson" wrote: 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 ' 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incremental Numbering: Compile Error - Variable not defined
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. BEEJAY wrote: 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" wrote: 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. "Dave Peterson" wrote: 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. "Dave Peterson" wrote: 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 ' 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incremental Numbering: Compile Error - Variable not defined
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. "Dave Peterson" wrote: 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. BEEJAY wrote: 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" wrote: 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. "Dave Peterson" wrote: 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. "Dave Peterson" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Microsoft Visual Basic: Compile error: Sum or Function not defined | Excel Worksheet Functions | |||
Incremental numbering | Excel Discussion (Misc queries) | |||
Variable not defined compile error | Excel Programming | |||
Compile error, variable not defined | Excel Programming | |||
compile error: expected variable or function | Excel Discussion (Misc queries) |