Trying to add multiple values based off of a specific text

[Baijano23]

Hello all!

I’m fairly new to using VBA but trying to get as much out of it as possible! I’m working on a code that will search for a specific phrase (ADD-CAS) in a cell and add a row above it. Once that is done, I then want to an add “Loaded” to a cell 6 cells above and 23 cells to the right of ADD-CAS. I have this working ok with the below code. Where I’m struggling is I also need to add “Dispensed”, “Expected”, “Reported” and “Variance” in that order, below “Loaded”. I’ve tried copying the same code but adding -5 for “Dispensed” however that just seems to overwrite the “Loaded” and then only shows “Dispensed”

What do I need to do to show all 5 of the phrases mentioned above at the same time?

The code I have so far:

Dim m As Long

Dim Lastrow2 As Long

Lastrow2 = Cells(Rows.Count, "D").End(xlUp).Row

For m = Lastrow2 To 1 Step -1

If Cells(m, "D").Value = "ADD-CAS" Then Cells(m, "D").EntireRow.Insert xlShiftDown

Next

Application.ScreenUpdating = True

With Range("D8", Range("D" & Rows.Count).End(xlUp))

.Offset(-6, 21).Value = Evaluate("if(isnumber(search(""ADD-CAS""," & .Address & ")),""Loaded"","""")")

End With


I’ve posted via my mobile so apologies if this has formatted terribly!

 

[DanteAmor]

Welcome to the MrExcel forum. Please accept my warmest greetings

I'm not sure if you need to put the first value 6 cells before or 5 cells before, since you have to consider that you inserted a row.
Anyway you can change the -5 to -6.

Sub AddValues()
  Dim i As Long
  For i = Range("D" & Rows.Count).End(3).Row To 1 Step -1
    With Range("D" & i)
      If .Value = "ADD-CAS" Then
        .EntireRow.Insert xlShiftDown
        .Offset(-5, 23).Resize(5).Value = _
          Application.Transpose(Array("Loaded", "Dispensed", "Expected", "Reported", "Variance"))
      End If
    End With
  Next
End Sub

If it is not what you need, you could put 2 images, the first image with your original data and the second image with the expected result of the macro.

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --