Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2007
    Location
    Washington D.C.
    Posts
    24

    Unanswered: VBA to Automate the "Fill" Menu Option?

    Greetings,

    I usually hang out over in the MS Access forum area, but I have to segway over to Excel for my current problem.

    I have a number of spreadsheets (200+) which I must clean up (for lack of a better word) so that I can import it into my MS Access database.

    My office is in the process of going through years' worth of data in spreadsheet form and getting it into a database for easier querying, etc...

    Anyway... my problem is this:
    * The spreadsheets are created from an automated system, the format of which is dictated by a higher authority, and I cannot request a change to the format
    * The first column of the spreadsheet (TAC) is not fully populated... in other words, if the first 15 rows are associated with a particular TAC, the TAC is listed in the first record, and the remainder are blank until the next series of records with a new TAC appears (this'll make much more sense if you look at the attachment)
    * Before I can import it into my database, I need to copy the TAC down through column A until I hit a new TAC.
    * So far, I've been highlighting the applicable area, hitting CTRL-D, to copy it down, and then moving along.

    This takes WAAAY too long.

    The spreadsheet I uploaded is a small fraction of a "real" spreadsheet. The real ones have around 15-20K rows and 20-30 TAC's on each. And I've got around 200 spreadsheets to do.

    This seems like it should be easy, and although I'm fairly handy with VBA in Access, today is my first foray into Excel VB. Needless to say, it's not going well.

    My rough VB pseudo code is below....Think I'm on the right track, but obviously my syntax is pretty much imaginary at this point... If someone could point me in the right direction, I'd be very appreciative!

    Sub Copy_TAC()
    Dim CurCell As Object
    For Each CurCell In Selection
    ' If the cell below the current cell is blank
    ' then copy the data to the cell below it
    ' else skip to the next cell
    ' endif
    Next
    End Sub

    Thanks for any assistance!

    -Dean (a.k.a. Friz)
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    This will fill down the values. I would set this up in an Addin file or hidden file with a button to activate the code. I've set this up in the attached file. When you open the attachment select 'Enable Macros' then you'll see the attached 'TAC Fill Down' Toolbar has 2 buttons to Run and close the file.

    Code:
    Sub TACFillDown()
    
    ' Enter a variable for the column to check for the last used Row
    ' I used column 2 per your sample
    nCol = 2
    
    With ActiveSheet
      lRow = .Cells(65535, nCol).End(xlUp).Row
      
      For i = 2 To lRow
        strCell = Trim(.Cells(i, 1))
        If Len(strCell) > 0 Then
          strCurTAC = strCell
          strCell = ""
        End If
        .Cells(i, 1) = strCurTAC
      
        ' Selects every 10th row So you can see operation
        ' You can comment out the next line its optional
       If i Mod 10 = 0 Then .Cells(i, 1).Select
        
      Next
    End With
    
    End Sub
    The workbook is hidden so you will see the toolbar but not the workbook worksheet. Open the VB Editor to view the code contained in the file.
    Attached Files Attached Files
    ~

    Bill

  3. #3
    Join Date
    Oct 2007
    Location
    Washington D.C.
    Posts
    24
    Bill,

    Bless you!!!!

    This is quite impressive, and the button is an added bonus that I wasn't expecting!

    I've been messing around with this since around 11 this morning and finally posted out of sheer desparation.

    There is a much bigger learning curve than I expected coming from Access VBA. I suspect I will be going to the store tomorrow to purchase a book...

    Thanks again for your assistance. It is very much appreciated indeed!

    -Dean

  4. #4
    Join Date
    Feb 2004
    Posts
    533
    Dean,

    you have an advantage already knowing Access VB. You need to learn the Excel Object Model and a few tricks and you'll be coding Excel with no problem. I first learned Excel VB then had to learn Access when I was assigned an Access project. I agree it is a good idea to pick up a book and read to learn new skills. Also remember the F1 key is your friend.
    ~

    Bill

Posting Permissions

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