Results 1 to 7 of 7

Thread: Formatting Macro Assistance

  1. #1

    Formatting Macro Assistance



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi there- I have a question regarding my formatting macro. Bit of background: on one sheet I have created a module where the user selects a set of values (some in a list form) and enters in texts based off of 7 cells. Once those values are entered, the user then presses "submit". Now, what is supposed to happen is that the values transfer to another sheet where it lists their entries into a chart format. When I did the formatting macro, all I did was select a cell, press '=' click the previous sheet, select the cell it needs to copy to and press enter:

    Range("B3").Select
    Sheets("Forecasting Module").Select
    Range("F12").Select

    This is repeated 7 times for all values, then I stopped recording and assigned the macro to the button- and it works (kinda). My issue is that when I try to submit a second entry with different values, it overrides my previous entry, and I need it to continuously add on my second sheet. Any help is greatly appreciated! Here is the macro currently used:

    Code:
    Sub Forecasting1()
     '
     ' Forecasting1 Macro
     '
    
     '
        Range("B3").Select
        Sheets("Forecasting Module").Select
        Range("F12").Select
        Sheets("Test Macro").Select
        Range("B3").Select
        ActiveCell.FormulaR1C1 = "='Forecasting Module'!R[3]C[2]"
        Range("C3").Select
        ActiveCell.FormulaR1C1 = "='Forecasting Module'!R[8]C[1]"
        Range("D3").Select
        ActiveCell.FormulaR1C1 = "='Forecasting Module'!R[8]C[5]"
        Range("E3").Select
        ActiveCell.FormulaR1C1 = "='Forecasting Module'!R[3]C[4]"
        Range("F3").Select
        ActiveCell.FormulaR1C1 = "='Forecasting Module'!R[13]C[-2]"
        Range("G3").Select
        ActiveCell.FormulaR1C1 = "='Forecasting Module'!R[3]C[6]"
        Range("H3").Select
        ActiveCell.FormulaR1C1 = "='Forecasting Module'!R[8]C[5]"
        Rows("4:4").Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("D3").Select
        Selection.NumberFormat = "General"
        Range("G3:H3").Select
        Selection.NumberFormat = "General"
        Range("E6").Select
     End Sub
    Last edited by Bob Phillips; 2014-07-25 at 11:19 AM. Reason: Added VBA tags

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    675
    Articles
    0
    Excel Version
    Excel 2010 64bit
    are you trying to take 7 values from Sheets("Forecasting Module") and put them into the third row of Sheets("Test Macro") starting in column B?
    if so, what are the 7 cell addresses on "Forecasting Module" ?

  3. #3
    Maybe this

    Code:
    Sub Forecasting1()
    Dim lastrow As Long
    
        With Worksheets("Test Macro")
        
            lastrow = .cell(.Rows.Count, "B").End(xlUp).Row
            .Cells(lastrow + 1, "B").FormulaR1C1 = "='Forecasting Module'!R[3]C[2]"
            .Cells(lastrow + 1, "C").FormulaR1C1 = "='Forecasting Module'!R[8]C[1]"
            .Cells(lastrow + 1, "D").FormulaR1C1 = "='Forecasting Module'!R[8]C[5]"
            .Cells(lastrow + 1, "E").FormulaR1C1 = "='Forecasting Module'!R[3]C[4]"
            .Cells(lastrow + 1, "F").FormulaR1C1 = "='Forecasting Module'!R[13]C[-2]"
            .Cells(lastrow + 1, "G").FormulaR1C1 = "='Forecasting Module'!R[3]C[6]"
            .Cells(lastrow + 1, "H").FormulaR1C1 = "='Forecasting Module'!R[8]C[5]"
        End With
    End Sub

  4. #4
    @NoS: Correct, the 7 values from sheet “Forecasting Module” will transfer to sheet “Test Macro” starting on cell B to cell H once I press the submit button. The issue is that the macro does not build on “Test Macro” if I submit something else, it just overwrites my previous submission.

    @Bob Phillips: Thanks for the code. Unfortunately, I am receiving a run time error 438 “object doesn't support this property or method”. Any idea how to debug?
    Paul

  5. #5
    I missed an s


    Code:
    Sub Forecasting1()
    Dim lastrow As Long
    
        With Worksheets("Test Macro")
    
            lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
            .Cells(lastrow + 1, "B").FormulaR1C1 = "='Forecasting Module'!R[3]C[2]"
            .Cells(lastrow + 1, "C").FormulaR1C1 = "='Forecasting Module'!R[8]C[1]"
            .Cells(lastrow + 1, "D").FormulaR1C1 = "='Forecasting Module'!R[8]C[5]"
            .Cells(lastrow + 1, "E").FormulaR1C1 = "='Forecasting Module'!R[3]C[4]"
            .Cells(lastrow + 1, "F").FormulaR1C1 = "='Forecasting Module'!R[13]C[-2]"
            .Cells(lastrow + 1, "G").FormulaR1C1 = "='Forecasting Module'!R[3]C[6]"
            .Cells(lastrow + 1, "H").FormulaR1C1 = "='Forecasting Module'!R[8]C[5]"
        End With
    End Sub

  6. #6
    Thanks again, it worked but I am still having some issues. The macro is working in the sense that it is building on my "Test Macro" sheet. However, when I submit a second or third forecast, the previous one turns to 0's. Here is a paste of what is occurring: (note: that the top line is the most recent submission, and the bottom 2 were the previous ones.) Appreciate the help.

    FORECASTING TRACKER
    Target Version Client Project Name Forecast Type Forecast (in days) Tracking # IAP
    2.3.1 EX1 Example 2 Approved for Placeholder 750 1855 No
    0 0 ='Forecasting Module'!R[8]C[5] 0 0 0 0
    0 0 ='Forecasting Module'!R[8]C[5] 0 0 0 0

  7. #7
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    675
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Paul, in my first post I asked what those 7 addresses were. Reason for that was that you write 7 times, but the third and seventh appear to be the same.

    To write to the top of your data every time, go back to what you posted originally and change Rows("4:4") back to Rows("3:3"), the way you had it in your first posting of this question at another forum.

Posting Permissions

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