Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Auto Assign Numbers in Sequence

  1. #1
    Join Date
    28th August 2005
    Location
    Seattle
    Posts
    17

    Auto Assign Numbers in Sequence

    Hello,

    I am currently using a command button that uses the now() function formatted as a number to generate a unique po# in a protected cell. Is there a way to create a po log and assign a macro to it that will assign the first number in the list and continue through the list each time the command button is used?

    The log would look something like this:
    606500
    606501
    606502
    606503
    606504
    606505
    606506
    606507

    Thank you!!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    5th May 2005
    Location
    University of Kent!!, UK
    Posts
    988

    Re: Auto Assign Numbers in Sequence

    Can you post your current code so we can get an idea of something to build on?

    Alastair
    Einstein:
    Things should be made as simple as possible, but not any simpler

    Be sure to check out TemplateZone for all your Microsoft Office Needs.
    Get OfficeReady Professional 3.0 here!

  3. #3
    Join Date
    28th August 2005
    Location
    Seattle
    Posts
    17

    Re: Auto Assign Numbers in Sequence

    Here it is. Thank you!

    Private Sub Workbook_Open()
    Dim FName As String
    Dim FNo As String
    Dim x As Long
    FName = ThisWorkbook.Path & Application.PathSeparator & "Number.Txt"
    FNo = FreeFile
    x = 1
    On Error Resume Next
    Open FName For Input As #FNo
    Input #FNo, x
    x = x + 1
    ' *** Change range reference to suit ***
    Range("C6:F7").Value = x
    Close #FNo
    FNo = FreeFile
    Open FName For Output As #FNo
    Write #1, x
    Close #FNo
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    ActiveSheet.Paste
    Application.CutCopyMode = False
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Selection.Merge
    End Sub

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    28th August 2005
    Location
    Seattle
    Posts
    17

    Re: Auto Assign Numbers in Sequence

    Here's another one I have.......... what I'd like is to be able to track the numbers in a log.

    Sub PO()
    '
    ' PO Macro
    ' Macro recorded 8/19/2005
    '

    ActiveSheet.Unprotect Password:="test"
    Range("O6:R7").Select
    ActiveCell.FormulaR1C1 = "=NOW()"
    Range("O6:R7").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = True
    End With
    Selection.UnMerge
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Selection.Merge
    ActiveSheet.Protect Password:="test"
    End Sub

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Retain Auto Number Sequence After Row Insert
    By Jeff P in forum EXCEL HELP
    Replies: 10
    Last Post: January 14th, 2013, 00:48
  2. Automatically Generate Numbers In Sequence
    By The_Beardy_One in forum EXCEL HELP
    Replies: 2
    Last Post: December 10th, 2007, 21:35
  3. Auto Re-assign Macros To Command Buttons
    By Mark_NJ in forum EXCEL HELP
    Replies: 2
    Last Post: August 7th, 2007, 05:15
  4. Count Numbers In Sequence
    By Vlad999 in forum EXCEL HELP
    Replies: 13
    Last Post: December 11th, 2006, 06:24
  5. Formulas : Sequence of numbers extraction
    By skelum in forum EXCEL HELP
    Replies: 3
    Last Post: February 6th, 2004, 00:38

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno