Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment Alpha Numeric Cell
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment Alpha Numeric Cell
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment Alpha Numeric Cell
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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
remove alpha or non-numeric characters from cell | Excel Discussion (Misc queries) | |||
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 | |||
The colums changed from alpha to numeric how do you make it alpha | Excel Discussion (Misc queries) |