Increment Alpha Numeric Cell

[Guest]

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

 

[Guest]

Try:

With WorkBooks("Automation Quote").Worksheets("Sheet1")


With Workbooks("Quote Reference Numbers"). _

 

[Guest]

No.....didn't work....still get the same error. Do you know any other way to
accomplish this?

 

[Guest]

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?

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

(e-mail address removed)

 

[Guest]

I emailed you the files......

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.