Results 1 to 6 of 6

Thread: Help with repeatative steps

  1. #1

    Help with repeatative steps



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

    Hi I am doing calculations of number of reducers i.e 10. However evertime I do calc for the next reducer, I will have to copy and paste to the table with reducer number (see attached workbook). Recently I had to work with about 80 reducers and this is some what cumbersome.

    All want to know is there a way I can input data on the green shaded areas and change it as per reducer size and excel will copy the required data to corresponding reducer number automatically.

    Reducer 1, D= 1500mm, d1=1200mm and calculated x=1050mm
    Reducer 2, D= 1000mm, d1=900mm and calculated x=350mm
    Reducer 3, D= 1000mm, d1=800mm and calculated x=700mm

    Your help will be appreciated
    Attached Files Attached Files

  2. #2
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    Yes this can be done if I am understanding what you want.

    You want to only enter data in cells B4, B5, B7, B10, and have that data moved to your Reducer table.

    To do this you will need to create a macro to copy the data to the table after you enter it, then clear the green cells so you can enter new data.
    Are you familiar with macro's?

    I will try to get some code done tonight for you, and post again in the morning.

    Simi

  3. #3
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    First off, I have to say I'm not entirely sure what you have going on with some of the formulas, or where some of the data in the green fields was used.

    With that said, this should at least get you on the right track.

    Firstly, I moved your Total Losses for Bends, and the total of column L to Row 12, then started the table headers on row 13, and the first line of data will be on row 14. This makes it easier to update the total for column L.
    You can also add some formatting to the new rows for the reducer table if you so wish to have a border.
    I simply put this code in an ActiveX Command Button on the form.

    Code:
    Private Sub Testing_Click()
    
    
        Dim lastRow As Long
        
        With ActiveSheet
            
            lastRow = .Cells(.Rows.Count, "F").End(xlUp).Row + 1
            
            'set the data in the Reducer table.
            .Range("F" & lastRow) = "Reducer " & lastRow - 13
            .Range("G" & lastRow) = .Range("B5")
            .Range("H" & lastRow) = .Range("B4")
            .Range("I" & lastRow) = .Range("B9")
            .Range("L12").Formula = "=Sum(L14:L" & lastRow & ")"
            
            'clear the input area
            .Range("B4") = ""
            .Range("B5") = ""
            
            'move the active cell back to B4 to input more data
            .Range("B4").Activate
            
        End With
    
    
    End Sub

  4. #4
    Hi Simi
    I have paste the code to the VBA mode and it does copy new data as i have entered it. However it start at Reducer 13 not Reducer 1 and go down. Can you perhaps attach a copy of your worksheet you have created.

    Regards

  5. #5
    Hi Simi

    I have managed to figure it out, so ignore my previous post before this one. I have another question though. How can I make the copied data on the table to be automatic black, instead of the white font type?

    Regards

  6. #6
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    The white font seems to be conditional formatting you have on the sheet. If you enter new data in the green boxes the text in the table goes to black again.

Posting Permissions

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