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

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

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

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

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

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

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

Hi,
On your original post, the worksheet in the 2nd file was called
DATA but in your latest posting it is called SHEET1 ... is this the error?

If you want to post it to me (all w/books):

toppers@johntopley.fsnet.co.uk

"Theresa" wrote:

> 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

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