If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Excel > Auto Fill a range

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Aug 2012
Posts: 113
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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jan 2002
Location: Bay Area
Posts: 506
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Aug 2012
Posts: 113
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 6
Really this code helps me a lot too.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

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