Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2012
    Posts
    126

    Auto Fill a range

    I don't do much in excel and i'm hoping this tedious task can be much easier with a macro/vba. I am setting up a excel sheet that I can then import into access and use it as a table. The columns are for locations and are Rack Shelf Bin Box and the range is 1 A 1 A through 6 K 9 E. Is there a code I can run to auto fill this data in excel or do I need to do this manually.

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    507
    This code should do it.
    Code:
    Sub Location_Codes_List()
    'Build a table of location codes by inserting values starting with 1,A,1,A and end with 6,K,9,E
    'Each set of 4 characters represents a storage location: Rack, Shelf, Bin, and Box.
    
    Dim shelfChars As String, boxChars As String
    Dim rackChars As String, binChars As String, endPoint As String
    Dim rack1 As String, shelf1 As String, bin1 As String, box1 As String
    Dim j As Integer, k As Integer, m As Integer, n As Integer
    Dim rowTracker As Long, allDone As Boolean
    
        rowTracker = 1
        rackChars = "123456789"
        shelfChars = "ABCDEFGHIJK"
        binChars = "123456789"
        boxChars = "ABCDEFGHIJK"
        endPoint = "6K9E"
        
        Sheets("Sheet1").Columns("A:D").ClearContents
        
        For j = 1 To Len(rackChars)
            rack1 = Mid(rackChars, j, 1)
            
            For k = 1 To Len(shelfChars)
                shelf1 = Mid(shelfChars, k, 1)
                
                For m = 1 To Len(binChars)
                    bin1 = Mid(binChars, m, 1)
                    
                    For n = 1 To Len(boxChars)
                        box1 = Mid(boxChars, n, 1)
                        Cells(rowTracker, 1).Value = rack1
                        Cells(rowTracker, 2).Value = shelf1
                        Cells(rowTracker, 3).Value = bin1
                        Cells(rowTracker, 4).Value = box1
                        If rack1 & shelf1 & bin1 & box1 = endPoint Then allDone = True
                        If allDone Then Exit For
                        rowTracker = rowTracker + 1
                    Next
                    If allDone Then Exit For
                Next
                If allDone Then Exit For
            Next
            If allDone Then Exit For
        Next
                
        MsgBox "done"
        
    End Sub

  3. #3
    Join Date
    Aug 2012
    Posts
    126
    Thank you so much for this. Worked like a charm. Had to edit out a few locations from the code but this saved me a ton of time

  4. #4
    Join Date
    Jan 2013
    Posts
    6
    Really this code helps me a lot too.

Posting Permissions

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