Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment Alpha Numeric Cell
Hi:
I have two work books. One contains a template for a quote the other will contain only a quote #, date of the quote and customer name. When I open the quote template I want to have the quote # field look into the other file, find the last quote # used and increment by 1. When the quote template is saved or once completed, I want the quote #, date, and customer name to be written back to the other file. I am using the following but keep getting a "Subscript out of range" error. Assume Automation Quote.Xls, Sheet1 has the old quote number in cell A1, the company name in B1 Dim rng as Range, sNum as String, s as String Dim rng1 as Range, rng2 as Range With WorkBooks("Automation Quote.xls").Worksheets("Sheet1") set rng1 = .Range("A1") set rng2 = .Range("B1") End With With Workbooks("Quote Reference Numbers.xls"). _ Worksheets("Data") set rng = .Cells(rows.count,1).End(xlup) End With snum = format(clng(right(rng.value,4))+1,"0000") s = left(rng.value,len(rng.value)-4) & s set rng = rng.offset(1,0) rng.value = s rng.offset(0,1).Value = Date rng.offset(0,1).Numberformat = "mmm d, yyyy" rng.offset(0,2).Value = rng2 ' rng1.value = s ' update Automation Quote with new number? What is wrong? Any help would be appreciated. Thanks, Theresa |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment Alpha Numeric Cell
Try: With WorkBooks("Automation Quote").Worksheets("Sheet1") With Workbooks("Quote Reference Numbers"). _ "Theresa" wrote: Hi: I have two work books. One contains a template for a quote the other will contain only a quote #, date of the quote and customer name. When I open the quote template I want to have the quote # field look into the other file, find the last quote # used and increment by 1. When the quote template is saved or once completed, I want the quote #, date, and customer name to be written back to the other file. I am using the following but keep getting a "Subscript out of range" error. Assume Automation Quote.Xls, Sheet1 has the old quote number in cell A1, the company name in B1 Dim rng as Range, sNum as String, s as String Dim rng1 as Range, rng2 as Range With WorkBooks("Automation Quote.xls").Worksheets("Sheet1") set rng1 = .Range("A1") set rng2 = .Range("B1") End With With Workbooks("Quote Reference Numbers.xls"). _ Worksheets("Data") set rng = .Cells(rows.count,1).End(xlup) End With snum = format(clng(right(rng.value,4))+1,"0000") s = left(rng.value,len(rng.value)-4) & s set rng = rng.offset(1,0) rng.value = s rng.offset(0,1).Value = Date rng.offset(0,1).Numberformat = "mmm d, yyyy" rng.offset(0,2).Value = rng2 ' rng1.value = s ' update Automation Quote with new number? What is wrong? Any help would be appreciated. Thanks, Theresa |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment Alpha Numeric Cell
No.....didn't work....still get the same error. Do you know any other way to
accomplish this? "Toppers" wrote: Try: With WorkBooks("Automation Quote").Worksheets("Sheet1") With Workbooks("Quote Reference Numbers"). _ "Theresa" wrote: Hi: I have two work books. One contains a template for a quote the other will contain only a quote #, date of the quote and customer name. When I open the quote template I want to have the quote # field look into the other file, find the last quote # used and increment by 1. When the quote template is saved or once completed, I want the quote #, date, and customer name to be written back to the other file. I am using the following but keep getting a "Subscript out of range" error. Assume Automation Quote.Xls, Sheet1 has the old quote number in cell A1, the company name in B1 Dim rng as Range, sNum as String, s as String Dim rng1 as Range, rng2 as Range With WorkBooks("Automation Quote.xls").Worksheets("Sheet1") set rng1 = .Range("A1") set rng2 = .Range("B1") End With With Workbooks("Quote Reference Numbers.xls"). _ Worksheets("Data") set rng = .Cells(rows.count,1).End(xlup) End With snum = format(clng(right(rng.value,4))+1,"0000") s = left(rng.value,len(rng.value)-4) & s set rng = rng.offset(1,0) rng.value = s rng.offset(0,1).Value = Date rng.offset(0,1).Numberformat = "mmm d, yyyy" rng.offset(0,2).Value = rng2 ' rng1.value = s ' update Automation Quote with new number? What is wrong? Any help would be appreciated. Thanks, Theresa |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment Alpha Numeric Cell
With the change I suggested, your code worked for me. I know it's obvious but
check your worksheet names for leading/trailing blanks. And which statement does it error on? "Theresa" wrote: No.....didn't work....still get the same error. Do you know any other way to accomplish this? "Toppers" wrote: Try: With WorkBooks("Automation Quote").Worksheets("Sheet1") With Workbooks("Quote Reference Numbers"). _ "Theresa" wrote: Hi: I have two work books. One contains a template for a quote the other will contain only a quote #, date of the quote and customer name. When I open the quote template I want to have the quote # field look into the other file, find the last quote # used and increment by 1. When the quote template is saved or once completed, I want the quote #, date, and customer name to be written back to the other file. I am using the following but keep getting a "Subscript out of range" error. Assume Automation Quote.Xls, Sheet1 has the old quote number in cell A1, the company name in B1 Dim rng as Range, sNum as String, s as String Dim rng1 as Range, rng2 as Range With WorkBooks("Automation Quote.xls").Worksheets("Sheet1") set rng1 = .Range("A1") set rng2 = .Range("B1") End With With Workbooks("Quote Reference Numbers.xls"). _ Worksheets("Data") set rng = .Cells(rows.count,1).End(xlup) End With snum = format(clng(right(rng.value,4))+1,"0000") s = left(rng.value,len(rng.value)-4) & s set rng = rng.offset(1,0) rng.value = s rng.offset(0,1).Value = Date rng.offset(0,1).Numberformat = "mmm d, yyyy" rng.offset(0,2).Value = rng2 ' rng1.value = s ' update Automation Quote with new number? What is wrong? Any help would be appreciated. Thanks, Theresa |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment Alpha Numeric Cell
And I think this is required:
s = Left(rng.Value, Len(rng.Value) - 4) & sNum '<== instead of s ??? I have just rerun a test with your original code and it worked OK. "Theresa" wrote: Hi: I have two work books. One contains a template for a quote the other will contain only a quote #, date of the quote and customer name. When I open the quote template I want to have the quote # field look into the other file, find the last quote # used and increment by 1. When the quote template is saved or once completed, I want the quote #, date, and customer name to be written back to the other file. I am using the following but keep getting a "Subscript out of range" error. Assume Automation Quote.Xls, Sheet1 has the old quote number in cell A1, the company name in B1 Dim rng as Range, sNum as String, s as String Dim rng1 as Range, rng2 as Range With WorkBooks("Automation Quote.xls").Worksheets("Sheet1") set rng1 = .Range("A1") set rng2 = .Range("B1") End With With Workbooks("Quote Reference Numbers.xls"). _ Worksheets("Data") set rng = .Cells(rows.count,1).End(xlup) End With snum = format(clng(right(rng.value,4))+1,"0000") s = left(rng.value,len(rng.value)-4) & s set rng = rng.offset(1,0) rng.value = s rng.offset(0,1).Value = Date rng.offset(0,1).Numberformat = "mmm d, yyyy" rng.offset(0,2).Value = rng2 ' rng1.value = s ' update Automation Quote with new number? What is wrong? Any help would be appreciated. Thanks, Theresa |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment Alpha Numeric Cell
It is still not working, it is currently erroring on the first file name. If
I add the file extension, it gets to the second file, but always errors there. I have checked the file name, etc. Do I have to have the second file open already? I would not have a problem sending you the files, if that is acceptable to you. "Toppers" wrote: With the change I suggested, your code worked for me. I know it's obvious but check your worksheet names for leading/trailing blanks. And which statement does it error on? "Theresa" wrote: No.....didn't work....still get the same error. Do you know any other way to accomplish this? "Toppers" wrote: Try: With WorkBooks("Automation Quote").Worksheets("Sheet1") With Workbooks("Quote Reference Numbers"). _ "Theresa" wrote: Hi: I have two work books. One contains a template for a quote the other will contain only a quote #, date of the quote and customer name. When I open the quote template I want to have the quote # field look into the other file, find the last quote # used and increment by 1. When the quote template is saved or once completed, I want the quote #, date, and customer name to be written back to the other file. I am using the following but keep getting a "Subscript out of range" error. Assume Automation Quote.Xls, Sheet1 has the old quote number in cell A1, the company name in B1 Dim rng as Range, sNum as String, s as String Dim rng1 as Range, rng2 as Range With WorkBooks("Automation Quote.xls").Worksheets("Sheet1") set rng1 = .Range("A1") set rng2 = .Range("B1") End With With Workbooks("Quote Reference Numbers.xls"). _ Worksheets("Data") set rng = .Cells(rows.count,1).End(xlup) End With snum = format(clng(right(rng.value,4))+1,"0000") s = left(rng.value,len(rng.value)-4) & s set rng = rng.offset(1,0) rng.value = s rng.offset(0,1).Value = Date rng.offset(0,1).Numberformat = "mmm d, yyyy" rng.offset(0,2).Value = rng2 ' rng1.value = s ' update Automation Quote with new number? What is wrong? Any help would be appreciated. Thanks, Theresa |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment Alpha Numeric Cell
It still hangs on the second file name. Here is the exact code which is in
the first file..... Private Sub Workbook_Open() Dim rng As Range, sNum As String, s As String Dim rng1 As Range, rng2 As Range With Workbooks("Automation Quote-revising KENTS.xls").Worksheets("Quotation") Set rng1 = .Range("F1") Set rng2 = .Range("C10") End With With Workbooks("Quote Reference Numbers.xls").Worksheets("Sheet1") Set rng = .Cells(Rows.Count, 1).End(xlUp) End With sNum = Format(CLng(Right(rng.Value, 4)) + 1, "0000") s = Left(rng.Value, Len(rng.Value) - 4) & sNum Set rng = rng.Offset(1, 0) rng.Value = s rng.Offset(0, 1).Value = Date rng.Offset(0, 1).NumberFormat = "mmm d, yyyy" rng.Offset(0, 2).Value = rng2 ' rng1.value = s ' update Automation Quote with new number? End Sub "Toppers" wrote: With the change I suggested, your code worked for me. I know it's obvious but check your worksheet names for leading/trailing blanks. And which statement does it error on? "Theresa" wrote: No.....didn't work....still get the same error. Do you know any other way to accomplish this? "Toppers" wrote: Try: With WorkBooks("Automation Quote").Worksheets("Sheet1") With Workbooks("Quote Reference Numbers"). _ "Theresa" wrote: Hi: I have two work books. One contains a template for a quote the other will contain only a quote #, date of the quote and customer name. When I open the quote template I want to have the quote # field look into the other file, find the last quote # used and increment by 1. When the quote template is saved or once completed, I want the quote #, date, and customer name to be written back to the other file. I am using the following but keep getting a "Subscript out of range" error. Assume Automation Quote.Xls, Sheet1 has the old quote number in cell A1, the company name in B1 Dim rng as Range, sNum as String, s as String Dim rng1 as Range, rng2 as Range With WorkBooks("Automation Quote.xls").Worksheets("Sheet1") set rng1 = .Range("A1") set rng2 = .Range("B1") End With With Workbooks("Quote Reference Numbers.xls"). _ Worksheets("Data") set rng = .Cells(rows.count,1).End(xlup) End With snum = format(clng(right(rng.value,4))+1,"0000") s = left(rng.value,len(rng.value)-4) & s set rng = rng.offset(1,0) rng.value = s rng.offset(0,1).Value = Date rng.offset(0,1).Numberformat = "mmm d, yyyy" rng.offset(0,2).Value = rng2 ' rng1.value = s ' update Automation Quote with new number? What is wrong? Any help would be appreciated. Thanks, Theresa |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment Alpha Numeric Cell
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment Alpha Numeric Cell
I emailed you the files......
"Toppers" wrote: And I think this is required: s = Left(rng.Value, Len(rng.Value) - 4) & sNum '<== instead of s ??? I have just rerun a test with your original code and it worked OK. "Theresa" wrote: Hi: I have two work books. One contains a template for a quote the other will contain only a quote #, date of the quote and customer name. When I open the quote template I want to have the quote # field look into the other file, find the last quote # used and increment by 1. When the quote template is saved or once completed, I want the quote #, date, and customer name to be written back to the other file. I am using the following but keep getting a "Subscript out of range" error. Assume Automation Quote.Xls, Sheet1 has the old quote number in cell A1, the company name in B1 Dim rng as Range, sNum as String, s as String Dim rng1 as Range, rng2 as Range With WorkBooks("Automation Quote.xls").Worksheets("Sheet1") set rng1 = .Range("A1") set rng2 = .Range("B1") End With With Workbooks("Quote Reference Numbers.xls"). _ Worksheets("Data") set rng = .Cells(rows.count,1).End(xlup) End With snum = format(clng(right(rng.value,4))+1,"0000") s = left(rng.value,len(rng.value)-4) & s set rng = rng.offset(1,0) rng.value = s rng.offset(0,1).Value = Date rng.offset(0,1).Numberformat = "mmm d, yyyy" rng.offset(0,2).Value = rng2 ' rng1.value = s ' update Automation Quote with new number? What is wrong? Any help would be appreciated. Thanks, Theresa |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I copy numeric section of cell from alpha-numeric cell | Excel Worksheet Functions | |||
only extract numeric value from alpha numeric cell | Excel Discussion (Misc queries) | |||
Can you ID a cell that has both Alpha AND Numeric characters? | Excel Worksheet Functions | |||
Increment Alpha Numeric Cell | Excel Programming | |||
The colums changed from alpha to numeric how do you make it alpha | Excel Discussion (Misc queries) |