Increment Alpha Numeric Cell

Hi:

I have a spreadsheet where I need to have a cell, a quote # (KF-2006-1001)
increment by 1 based on the last used number. I want to store this data in a
separate file (Quote reference Numbers). How do I have the cell look up into
my quote reference number file to find the last quote #, then add 1, then
store that value back into the quote reference number file on the next
available row? I also want to write other fields back into the same file.

Thanks....any help will be appreciated.
--
Theresa

assume the activecell has the current number you want to increment.

as long as they are in order

Dim rng as Range, sNum as String, s as String
With Workbooks("Quote Reference Numbers.xls"). _
Worksheets("Data")
set rng = .Cells(rows.count,1).End(xlup)
if rng.value <> activeCell.Value then
msgbox "Problems"
exit sub
End if
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
ActiveCell.Value = s


If you can't assume they are in order
Dim rng as Range, rng1 as Range, sNum as String
Dim s as String
With Workbooks("Quote Reference Numbers.xls"). _
Worksheets("Data")
set rng = .Cells(rows.count,1).End(xlup)(2)
set rng1 = .Columns(1).Find(activeCell.Value)
if rng1 is nothing then
msgbox "Problems"
exit sub
End if
End With
snum = format(clng(right(rng1.value,4))+1,"0000")
s = left(rng1.value,len(rng1.value)-4) & s
rng.value = s
ActiveCell.Value = s



code is untested and may contain typos.

--
Regards,
Tom Ogilvy


"Theresa" <Theresa@discussions.microsoft.com> wrote in message
news:1A10FA14-02EF-4FC6-8F3E-9A76C2374A58@microsoft.com...
> Hi:
>
> I have a spreadsheet where I need to have a cell, a quote # (KF-2006-1001)
> increment by 1 based on the last used number. I want to store this data
in a
> separate file (Quote reference Numbers). How do I have the cell look up
into
> my quote reference number file to find the last quote #, then add 1, then
> store that value back into the quote reference number file on the next
> available row? I also want to write other fields back into the same file.
>
> Thanks....any help will be appreciated.
> --
> Theresa

Hi Tom:

The quote numbers will be in order, however, the active cell in the quote
reference number workbook may not be in the column containing the quote #'s.

Ex. of quote reference number file

Quote # Date Customer Name

KF-2006-1001 Jan 2, 2006 ABC Company
KF-2006-1002 Jan 4, 2006 Acme Inc.

The quote number must be generated on the row, and the date and customer
name have to come from the actual quote file (Automation Quote).
--
Theresa


"Tom Ogilvy" wrote:

> assume the activecell has the current number you want to increment.
>
> as long as they are in order
>
> Dim rng as Range, sNum as String, s as String
> With Workbooks("Quote Reference Numbers.xls"). _
> Worksheets("Data")
> set rng = .Cells(rows.count,1).End(xlup)
> if rng.value <> activeCell.Value then
> msgbox "Problems"
> exit sub
> End if
> 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
> ActiveCell.Value = s
>
>
> If you can't assume they are in order
> Dim rng as Range, rng1 as Range, sNum as String
> Dim s as String
> With Workbooks("Quote Reference Numbers.xls"). _
> Worksheets("Data")
> set rng = .Cells(rows.count,1).End(xlup)(2)
> set rng1 = .Columns(1).Find(activeCell.Value)
> if rng1 is nothing then
> msgbox "Problems"
> exit sub
> End if
> End With
> snum = format(clng(right(rng1.value,4))+1,"0000")
> s = left(rng1.value,len(rng1.value)-4) & s
> rng.value = s
> ActiveCell.Value = s
>
>
>
> code is untested and may contain typos.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Theresa" <Theresa@discussions.microsoft.com> wrote in message
> news:1A10FA14-02EF-4FC6-8F3E-9A76C2374A58@microsoft.com...
> > Hi:
> >
> > I have a spreadsheet where I need to have a cell, a quote # (KF-2006-1001)
> > increment by 1 based on the last used number. I want to store this data
> in a
> > separate file (Quote reference Numbers). How do I have the cell look up
> into
> > my quote reference number file to find the last quote #, then add 1, then
> > store that value back into the quote reference number file on the next
> > available row? I also want to write other fields back into the same file.
> >
> > Thanks....any help will be appreciated.
> > --
> > Theresa
>
>
>

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?

--
Regards,
Tom Ogilvy


"Theresa" <Theresa@discussions.microsoft.com> wrote in message
news:9489F692-9653-4353-B339-28A9200E0923@microsoft.com...
> Hi Tom:
>
> The quote numbers will be in order, however, the active cell in the quote
> reference number workbook may not be in the column containing the quote
#'s.
>
> Ex. of quote reference number file
>
> Quote # Date Customer Name
>
> KF-2006-1001 Jan 2, 2006 ABC Company
> KF-2006-1002 Jan 4, 2006 Acme Inc.
>
> The quote number must be generated on the row, and the date and customer
> name have to come from the actual quote file (Automation Quote).
> --
> Theresa
>
>
> "Tom Ogilvy" wrote:
>
> > assume the activecell has the current number you want to increment.
> >
> > as long as they are in order
> >
> > Dim rng as Range, sNum as String, s as String
> > With Workbooks("Quote Reference Numbers.xls"). _
> > Worksheets("Data")
> > set rng = .Cells(rows.count,1).End(xlup)
> > if rng.value <> activeCell.Value then
> > msgbox "Problems"
> > exit sub
> > End if
> > 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
> > ActiveCell.Value = s
> >
> >
> > If you can't assume they are in order
> > Dim rng as Range, rng1 as Range, sNum as String
> > Dim s as String
> > With Workbooks("Quote Reference Numbers.xls"). _
> > Worksheets("Data")
> > set rng = .Cells(rows.count,1).End(xlup)(2)
> > set rng1 = .Columns(1).Find(activeCell.Value)
> > if rng1 is nothing then
> > msgbox "Problems"
> > exit sub
> > End if
> > End With
> > snum = format(clng(right(rng1.value,4))+1,"0000")
> > s = left(rng1.value,len(rng1.value)-4) & s
> > rng.value = s
> > ActiveCell.Value = s
> >
> >
> >
> > code is untested and may contain typos.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Theresa" <Theresa@discussions.microsoft.com> wrote in message
> > news:1A10FA14-02EF-4FC6-8F3E-9A76C2374A58@microsoft.com...
> > > Hi:
> > >
> > > I have a spreadsheet where I need to have a cell, a quote #
(KF-2006-1001)
> > > increment by 1 based on the last used number. I want to store this
data
> > in a
> > > separate file (Quote reference Numbers). How do I have the cell look
up
> > into
> > > my quote reference number file to find the last quote #, then add 1,
then
> > > store that value back into the quote reference number file on the next
> > > available row? I also want to write other fields back into the same
file.
> > >
> > > Thanks....any help will be appreciated.
> > > --
> > > Theresa
> >
> >
> >