Help with repeatative steps

J

justmcd

New member
Joined
Jan 3, 2012
Messages
39
Reaction score
0
Points
0
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
 

Attachments

  • Reducer.xlsx
    25.2 KB · Views: 16
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
 
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
 
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
 
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
 
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.
 
You must log in or register to reply here.
Back
Top