Adding a row above a cell with a specific character string (to add row to bottom of table) using VBA code in excel and a macros button.

Question

Hi, I have seen a few thread on this, however none have worked specifically for what I am trying to do.

I am create a template for a form that users will fill out in excel. The form is divided in sections and a few of these sections require an "Add New Row" button. I have the button set up and I am trying to program the code to add a new row at the end of the section. This new row should have the same formatting as the rows above. To do this I am referencing the cell below that has a specific text string in the cell, so that the new row is added above that cell. I have the following VBA code as of now. However, when I try to run the code by clicking the button, I get an error at the 8th line. I have posted a picture with the line highlighted. I am a beginner at all of this and am still learning VBA code. Is anyone able to help me with this please?

Thank you!

CODE:

Private Sub CommandButton1_Click()

Dim mySheets

Dim i As Long 

Dim NewRow As Long 

Const CharString As String = "Development Goals (Training, Career Growth)" 

mySheets = Array("Career Plan 2022") 

For i = LBound(mySheets) To UBound(mySheets) 

    If mySheets(i).Value = CharString Then 

        NewRow = mySheets(i).Row 

        With Sheets(mySheets(i)) 

            .Range("Ai").EntireRow.Insert shift:=xlDown 

            Rows("i-1:i-1").Copy Range("i") 

            Range("i:i").ClearContents 

        End With 

    End If 

Exit For 

Next i

End Sub

PICTURE:

Answer 1

How about

Private Sub CommandButton1_Click()
    Dim wsh As Worksheet
    Dim rng As Range
    Dim NewRow As Long
    Const CharString As String = "Development Goals (Training, Career Growth)"

    Set wsh = Worksheets("Career Plan 2022")
    Set rng = wsh.Range("A:A").Find(What:=CharString, LookAt:=xlWhole)
    If Not rng Is Nothing Then
        rng.EntireRow.Insert
        rng.Offset(-2).EntireRow.Copy
        rng.Offset(-1).EntireRow.PasteSpecial Paste:=xlPasteFormats
        Application.CutCopyMode = False
    End If
End Sub

Answer 2

Change that line to

    If mySheets(i) = CharString Then

Answer 3

Hi HansV MVP,

Thank you so much for your reply. I made this change, and it no longer created an error in the code. However, now when I click the button, nothing happens at all.

Answer 4

Your array mySheets has one element: "Career Plan 2022"

This is not equal to the value of CharString, so the part between If ... and End If is not executed.

What did you expect?

Answer 5

I thought that setting mySheets = Array("Career Plan 2022") would set the overall range, the for statement searches that range for the cell of value i within that range that matches the character string. I am new to this, so I am still trying to make sense of how VBA functions work. What would you recommend I do to have this same process be carried out?