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

Question

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

Navigation

#1 by (1 votes)

2

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
            Else
        MsgBox "Error", vbExclamation
            End If
    Next itm
End If

End Sub

vba macros excel

asked by stefa zapata 11.07.2018 в 17:40

source

1 answer

Error sending my project to Angular production Count the input [] in javascripts

score 1 · Accepted 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.

Range("A1").Select
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
    Else
        num = num + 1
        ActiveCell.Offset(1, 0).Select
    End If
Wend
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.