Results 1 to 2 of 2

How to code repeating vba formatting/cell merging

Threaded View

  1. #1
    Registered User
    Join Date
    09-27-2013
    Location
    Idaho
    MS-Off Ver
    Excel 2007
    Posts
    1

    How to code repeating vba formatting/cell merging

    Sub dowhile()
        Dim x As Integer, x1 As Integer, row As Integer, row1 As Integer
        Dim col As Integer, col1 As Integer, col2 As Integer, col3 As Integer, col4 As Integer, t As Integer, t1 As Integer
        Dim y As String, z As String, x4 As String, w As String
                      
        t = Workbooks("weeksumry.xlsm").Worksheets("sheet1").Range("h1").Value + 1
        row = t        'pointer to next available row in weeksumry
        col = 1
        z = ThisWorkbook.Worksheets("Order Form").Range("a6")   'Read agency name
        'col4 = 4
    'This code module creates a summary sheet for weekly deliveries to various welfare agencies around our valley. We make an
    'excel delivery sheet
    'for each agency as we deliver to them, and I'm trying to summarize our weekly deliveries from those sheets for inventory control and etc..
    'The delivery sheets consist, first, of the agency name info followed by item quanities and descriptions. There are 12 or fewer items
    'per sheet. The delivery sheet info is concatenated into four columns on the summary sheet. The agency info requires more space so needs
    'to go into merged cells. So then the agency info is in the merged cells followed by the item quanities and their descriptions'

    HERE IS MY PROB. - the code in the next line properly merges cells a1:d1 which holds the agency name for the first delivery
    'sheet processed but I need to change it/something to format/merge the cells used for subsequent agency names as their sheets are
    'processed. The deliveries are not all the same length so can't just format every nth row. Some agencies order more stuff than others.
    'The variable h1 is a counter placed on the "sumry" sheet which points to the last used row, so I add one to point to the next row available.
    'I'll strive for more coding elegance after I get this $*@%#& thing working. I now open each delivery sheet file manually. I'll next try to
    'step through the folder and open and process each file automatically. Typically about a dozen delivery files per week.


     Workbooks("weeksumry.xlsm").Worksheets("sheet1").Range("a1:d1").Select                   'THIS IS THE LINE I CAN'T FIX!
    
           With Selection
            .ColumnWidth = 4
            .VerticalAlignment = xlCenter
            .HorizontalAlignment = xlCenter
            .MergeCells = True                '''Not through w this -need more rows to further adj col widths - later!
         End With
        Workbooks("weeksumry.xlsm").Worksheets("sheet1").Cells(row, col).Value = z     'Write agency name to weeksumry
        For row = 8 To 30      'Read all the rows on thisworkbook
            col1 = 1             'col*  point to source columns in source file
            col2 = col + 1
            col3 = col + 2
            col4 = col1 + 3
         'Read columns from source file
            x1 = ThisWorkbook.Worksheets("Order Form").Cells(row, col1).Value
            x2 = ThisWorkbook.Worksheets("Order Form").Cells(row, col2).Value
            x4 = ThisWorkbook.Worksheets("Order Form").Cells(row, col4).Value
            x3 = x1 + x2
                                  
            row1 = row - 7 + t     'Row number on destination page
            If x1 + x2 = 0 Then GoTo continue
                 Workbooks("weeksumry.xlsm").Worksheets("sheet1").Cells(row1, col1).Value = x1      'Write values to weeksumry
                 Workbooks("weeksumry.xlsm").Worksheets("sheet1").Cells(row1, col2).Value = x2
                 Workbooks("weeksumry.xlsm").Worksheets("sheet1").Cells(row1, col3).Value = x3
                 Workbooks("weeksumry.xlsm").Worksheets("sheet1").Cells(row1, col4).Value = x4
    continue:
            row = row + 1   'Adjust row to skip empty source file row
        Next row            'Read and copy next row
      End Sub
    Thank you, thank you for any suggestions.
    Last edited by alansidman; 11-07-2013 at 11:15 AM. Reason: code tags added

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Repeating code
    By Bob316 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-24-2010, 03:00 AM
  2. Help with repeating the code
    By rusoo7 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-23-2009, 07:05 AM
  3. Repeating code
    By Myrmecophaga in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-31-2008, 06:57 AM
  4. Merging code - Validation and Formatting
    By Sirishgreen in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-20-2007, 07:22 AM
  5. Repeating code
    By mandeepuppal in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-18-2006, 06:50 AM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1