Results 1 to 5 of 5

Minor modification to existing macro (insert data without clearing contents of rows above)

Threaded View

  1. #1
    Forum Contributor
    Join Date
    01-08-2013
    Location
    Goergia USA
    MS-Off Ver
    Excel 2007
    Posts
    102

    Minor modification to existing macro (insert data without clearing contents of rows above)

    Hi all!

    I am on the final stretch of my project, and thought I had a better handle on creating macros, but I am having a tough time figuring this out. Here is the issue I am having. Please help!!

    I have two macros in this spreadsheet: (I have command buttons in the ribbon -- "Pre-Payment Macros")
    1. Insert Pre-Payment Tab
    2. Insert Data

    When I run the Insert Pre-Payment Tab macro, it works great. But, when I run the Insert Data macro afterward, it deletes the second line of my table (it deletes the contents of row 2).

    So, there is something in the second macro that needs to be edited.

    When the first macro is run, the cursor is in cell A3, which is great. I need the second macro to place the data into cell A3 (without clearing the contents of row 2).

    Any help is greatly appreciated! I am attaching the spreadsheet, along with the code for the second macro below.

    Sub PrePaymentTableDataInput()
        Dim myAreas As Areas, i As Long, ii As Long
        Set myAreas = Sheets("summary").Columns(2).SpecialCells(2, 1).Areas
        ReDim a(1 To myAreas.Count + 1, 1 To 6)
        a(UBound(a, 1), 1) = "Total"
        For i = 1 To myAreas.Count
            With myAreas(i)
                a(i, 1) = myAreas(i)(-1, 0).Value
                a(i, 2) = .Cells(, 1).Value + .Cells(, 3).Value
                a(i, 3) = .Cells(, 2).Value + .Cells(, 4).Value + .Cells(, 5).Value
                a(i, 4) = .Cells(2, 1).Value + .Cells(2, 3).Value
                a(i, 5) = .Cells(2, 2).Value + .Cells(2, 4).Value + .Cells(2, 5).Value
                For ii = 2 To 5
                    a(UBound(a, 1), ii) = a(UBound(a, 1), ii) + a(i, ii)
                    a(i, 6) = a(i, 6) + a(i, ii)
                Next
            End With
        Next
        With Sheets("Pre-payment")
            .Range("a3", .Cells.SpecialCells(11)).Resize(, 6).Clear
            With .Range("a3").Resize(UBound(a, 1), 6)
                .Value = a
                .NumberFormat = """$""#,##0"
                With .Rows(.Rows.Count)
                    .Cells(1).Font.Bold = True
                    .Resize(, 5).Borders(8).Weight = xlThin
                    .Resize(, 6).Borders(4).Weight = xlThin
                    .Resize(, 5).Borders(4).LineStyle = xlDouble
                    With .Cells(2, 5)
                        .Font.Bold = True
                        .Value = "TOTAL RETAINER"
                        .Range("b1").FormulaR1C1 = "=sum(r3c:r[-1]c)"
                    End With
                End With
            End With
        End With
    End Sub
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Insert X rows Macro modification
    By drgkt in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-07-2017, 04:07 PM
  2. Need minor modification in the formula
    By sweetfriend9 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-06-2016, 02:14 AM
  3. Minor reference modification...
    By Polymorpher in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-30-2016, 02:06 PM
  4. [SOLVED] Minor fix to existing macro - bottom borders not being generated properly
    By cmaunder in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-15-2014, 11:47 AM
  5. Macro Help for Deleting Blank Rows & Clearing Cell Contents
    By ksp in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-13-2006, 01:25 AM
  6. [SOLVED] minor modification to macro, need to delete cells
    By Michael A in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-04-2005, 11:06 PM

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