How to insert a new row with data in an Excel spreadsheet


I am looking for help in a code that is complicating me a lot since I must use (at least I think so) the Range method. Find , which I have not managed to know how it works.

What I need is a code with which you can enter new blank rows in an excel sheet, this row must be entered if certain conditions are met.

I have a excel sheet that contains "Subchapters" and "Items" , there is a possibility that new Items should be created and for that the user has the following form:

The code so far reads the data from the field "Insert the Name of the Subchapter" and it will look for the sheet "BDusuario1"

Now what follows (and is the part where I need help) is that once the search object is located the macro is due:

  • Move 2 columns to the left and read a code that is there find, store it in a variable and add 1
  • Then in another variable you should store the code that is found one row below the one mentioned above.
  • Then both variables must be compared and if both are equal variables should be moved one row down and do the same comparison; if they are different you should create a blank row in half of the 2 variables
  • Note: Codes can only arrive until they end at 99 for example 4011099 , if that limit was reached and no space was found to add the new item a message should be displayed informing you.

    I will attach a basic copy of the original file, since I can not send the original, but I hope it is enough for them to work. The important fields for this macro are the ones that I enclose in blue and I highlight in yellow, the rest can ignore it.

    Sample File

    I hope you have managed to make me understand, if you need more information or more clarity on something I will gladly try to give them to you.

    This is the part of the code where I need help, put comments on what each line should do for what I hope is better clarity.

        Cod = ActiveCell.Offset(0, -2) 'Aca intento hacer una vez encontrado el dato de la variable "nomSubc" se mueva 2 filas a la izquierda y lea un código que allí se encuentra
        '''Cod = a.Cells(fila, "A").Value
       For itm = 1 To 99
            Cod1 = ActiveCell.Value 'en esta parte lo que quiero es que una vez sea lea ese código se guarde en una variable
            Cod1 = Cod1 + 1         'y se le sume 1
            Cod2 = ActiveCell.Offset(1, 0) 'Aca lo que busco que se lea en código que esta de bajo del que se guardo en la variable "Cod1"
                If Cod1 <> Cod2 Then            'En estás lineas se debe hacer una comparacion,
                    ActiveCell.EntireRow.Insert 'Si son diferentes se debe crear una fila vacia en la mitad de Cod1 y Cod2
                    'ActiveCell.Offset(2, 0)    'Si Cod1 y Cod2 son iguales Cod1 debe pasar a leer Cod2 y Cod2 debe pasar a leer el código de abajo
            MsgBox "Error", vbExclamation
                End If
        Next itm
    End If
    End Sub
    asked by stefa zapata 11.07.2018 в 17:40

    1 answer


    Create a small spreadsheet with numbers in its first column ... and leave a gap in the middle:

    Then, I built a function to complete with a blank row, where 7 should be.

    Dim num As Integer
    num = ActiveCell.Value
    Dim valido As Boolean
    valido = True
    While num < 100 And valido
        'si, la primera comparacion es con si mismo
        If (num <> ActiveCell.Value) Then
            'encontramos la fila diferente, tenemos que agregar una arriba de esta
            valido = False
            Range(ActiveCell.Row & ":" & ActiveCell.Row).Select
            Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            num = num + 1
            ActiveCell.Offset(1, 0).Select
        End If
    If (valido) Then
        'aca sacamos el mensaje que no se pudo agregar
        MsgBox ("no se puede agregar")
    End If

    In the code, let's find A1, we keep that number, and scroll down, looking for where one is missing. The while cuts when it finds the hole, or reaches 100, whichever comes first.

    If I get to 100, then I send the message, for that I use the Boolean variable.

    I think that with this you can adapt your code to work.

    answered by 12.07.2018 / 19:24