Insert according to code, a new row that maintains the characteristics.

[Visor]

Dear friends of the forum, I would like you to be able to help me to make it possible to insert a new row according to the unit you select but write in the new row the forward code that is observed in column 3
I would like the entire new row to be inert to keep the same characteristics of the previous row so that it keeps working.
I appreciate all your collaboration
I upload a file to be seen

 

[Visor]

please suggest any ideas?

 

[Chihiro]

I'm a bit confused on your requirement. Can you demonstrate/describe it in more detail?

What should end result look like?

 

[Visor]

Hi I am going to explicate with images
first
I am sorry to have sent with an error every units are I, II, III and IV, respectively for C7, C14, C22 and C29.

In the userform I select the unit in which I want to insert a new row.
I hit the button
A new row is added (in this case in unit II)



second



Result: in A21 the established format winding3, In B21 U2TP6, C21 6, E21 11, D21 nothing, and the rest that the formula remains.
I hope I have explained better
Thank you for your collaboration.

 

[Visor]

Hi
This topic can not have solution?

 

[Chihiro]

Patience. I normally don't check forums on weekend and am bit busy at work today. If no one else has responded, I'll try and take a look tomorrow.

 

[Visor]

I'm sorry
Thank you

I have been trying to find the maximum value of the unit and the TP to enter a new one, but what happens if I want to place between another existing row, maybe I should choose the unit and the TP that was written in textbox in case the values are more than usual that is usually up to 10 mmmm, that is making me think that it would be better.

Anyway I appreciate what helps me to adapt it in the best way

 

[Chihiro]

Normally, I don't recommend this approach, as this sort of operation unnecessarily complicates code.

I'd use simple flat table as data entry point and then generate summary like "AvAcadem" sheet via code etc.

Lets think this in steps, first you need to identify row at which you need to insert row.

For that, you should use Range.Find method, starting at first cell in range and look backward (xlPrevious).

From there it's simple matter of using c's row# to do your copy, insert operation and update values in B to C range.

Private Sub CommandButton1_Click()
Dim sRange As Range, c As Range
With Sheets("AvAcadem")
    Set sRange = .Range("B7:B" & .Cells(Rows.Count, "B").End(xlUp).Row)
End With
With sRange
    Set c = .Find(What:="U" & Right(ComboBox1.Value, 1) & "TP", _
                    After:=.Cells(1), _
                    LookIn:=xlValues, _
                    LookAt:=xlPart, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False)
    If Not c Is Nothing Then
        With Sheets("AvAcadem")
            .Rows(c.Row).Copy
            .Rows(c.Row).Offset(1).Insert Shift:=xlDown
            .Range("A" & c.Row + 1 & ":" & "E" & c.Row + 1).Interior.Color = c.Offset(-1).Interior.Color
            .Range("B" & c.Row + 1 & ":" & "C" & c.Row + 1) = Array("U" & Right(ComboBox1.Value, 1) & "TP" & c.Offset(, 1).Value + 1, c.Offset(, 1).Value + 1)
        End With
    End If
End With
End Sub

For the values in Column E, you can just use formula so you don't have to update them via code.
=COUNTIF($B$9:B9,"*TP*")
Copied down.

See attached.

 

[Visor]

Thank you for helping me in this theme, it works as required, it's fine

"Normally, I don't recommend this approach, as this sort of operation unnecessarily complicates code."

It is true!!,. Maybe the best thing is to make a unit for each sheet, but to create it with macro the new sheet that shows the same headings, formulas, structure to fill with the contents of the sheet, it is difficult for me to solve especially because I use this data in a Form two lisbox in a tabstrip.
Currently I only have four units, but if I need to increase units will be a series of reforms in the vba code of the form to act with the new sheet, and that I find it difficult to automatically generate the internal interaction vba-new sheet ...
Is that the least complicated suggestion, ... what do you mean?

sorry for not expressing everything in English languaje

 

[Chihiro]

Via Code etc. So mostly etc

I usually create summary using PivotTable and/or PowerQuery. When and if necessary, I'd code logic using VBA to be flexible enough, using dictionary object and arrays to transform data.

 

[Visor]

"PivotTable and/or PowerQuery"

mmmm mm I am goin to read about this topics, sure I'll need it to improve my applications
Thanks again