1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Discussion in 'VBA Macros' started by Visor, Nov 9, 2017.

  1. Visor

    Visor Member

    Messages:
    140
    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

    Attached Files:

  2. Visor

    Visor Member

    Messages:
    140
    please suggest any ideas?
  3. Chihiro

    Chihiro Excel Ninja

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

    What should end result look like?
  4. Visor

    Visor Member

    Messages:
    140
    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)

    upload_2017-11-10_19-52-13.png

    second

    upload_2017-11-10_20-34-26.png

    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.

    Attached Files:

  5. Visor

    Visor Member

    Messages:
    140
    Hi
    This topic can not have solution?
  6. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,805
    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 likes this.
  7. Visor

    Visor Member

    Messages:
    140
    :) 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
  8. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,805
    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.

    Code (vb):
    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.

    Attached Files:

    Visor likes this.
  9. Visor

    Visor Member

    Messages:
    140
    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
  10. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,805
    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 likes this.
  11. Visor

    Visor Member

    Messages:
    140
    "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

Share This Page