Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 812
Default Sequential Number Generator Macro

See below. It assumes at least 1 entry in each of
21 columns; 1 column for each classification.

Hth,
Merjet

Sub NewCode()
Dim str1 As String
Dim str2 As String
Dim iNum As Integer
Dim iCol As Integer

On Error GoTo error_trap

str2 = "Enter class number -- 1-BLDG, 2-HSKG, 3-LCWM,"
str2 = str2 & " 4-MAIN, 5-UTIL , 6-LCWO, 7-ENRG, 8-AMGT,"
str2 = str2 & " 9-ITEA, 10-I&CS. 11-METR,12-EHSS, 13-EHSG,"
str2 = str2 & " 14-ITEH, 15-EHSR, 16-ITFP, 17-PLNG, 18-ACTG,"
str2 = str2 & " 19-ADMN, 20-SECT, 21-ITTC"
Do
'error will occur if InputBox returns blank
iCol = InputBox(str2)
iRow = Sheets("Sheet1").Cells(65536, iCol).End(xlUp).Row
str1 = Sheets("Sheet1").Cells(iRow, iCol)
iNum = Right(str1, Len(str1) - 4)
If iNum < 10 Then
str1 = Left(str1, 4) & "0" & iNum + 1
Else
str1 = Left(str1, 4) & iNum + 1
End If
Sheets("Sheet1").Cells(iRow + 1, iCol) = str1
Loop
error_trap:
End Sub
Sub

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 812
Default Sequential Number Generator Macro

I forget the coloring.
Add these lines before "Loop":

Sheets("Sheet1").Cells(iRow, iCol).Interior.ColorIndex = xlNone
Sheets("Sheet1").Cells(iRow, iCol).Font.ColorIndex = 0
Sheets("Sheet1").Cells(iRow + 1, iCol).Interior.ColorIndex = 6
Sheets("Sheet1").Cells(iRow + 1, iCol).Interior.Pattern = xlSolid
Sheets("Sheet1").Cells(iRow + 1, iCol).Font.ColorIndex = 3

Hth,
Merjet

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7
Default Sequential Number Generator Macro

Merjet,
You are the BEST!!!. Thanks for your help and patience!...The code
absolutely worked!

On Jan 24, 11:14 am, "merjet" wrote:
I forget the coloring.
Add these lines before "Loop":

Sheets("Sheet1").Cells(iRow, iCol).Interior.ColorIndex = xlNone
Sheets("Sheet1").Cells(iRow, iCol).Font.ColorIndex = 0
Sheets("Sheet1").Cells(iRow + 1, iCol).Interior.ColorIndex = 6
Sheets("Sheet1").Cells(iRow + 1, iCol).Interior.Pattern = xlSolid
Sheets("Sheet1").Cells(iRow + 1, iCol).Font.ColorIndex = 3

Hth,
Merjet


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
number generator how can i sequence the middle numbers Excel Discussion (Misc queries) 2 November 10th 08 11:01 PM
random number generator Barb Excel Discussion (Misc queries) 2 September 22nd 06 11:12 PM
same number appears in a random number generator Carmel Excel Worksheet Functions 4 May 28th 06 12:22 AM
Random Number Generator STEVEB Excel Programming 1 July 22nd 05 09:42 PM
sequential number generator Karen Westerman Excel Programming 2 October 21st 03 07:39 AM


All times are GMT +1. The time now is 04:59 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"