Insert new row on change, then formula

[hg1027]

So I got the code from vogII to add a new row after a change in a column (I in this case):

Dim LR As Long, i As Long
LR = Range("I" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
If Range("I" & i).Value <> Range("I" & i - 1).Value Then
Rows(i).EntireRow.Insert shift:=xlShiftDown
End If
Next i

And now I want to add a formula to the new blank cells in column B. The formula will be =IF(H3="FAB","FAB DWG "&G3,"INSTALL DWG "&G3).

Also, some of the text that comes in to my description field has more info than I need, which I text-to-columns to the first 4 empty columns (starts with K right now). I'd like to combine the last 3 (L,M,N) in B and then copy and paste values in B (this will take place before adding the rows and formula from above). I've almost got the combine and delete parts, but the combine only works for the first cell in my range. Also, I haven't put in the copy-paste values section because I'm about to have to leave.

Range("B2:B").Select
ActiveCell.FormulaR1C1 = "=RC[9]&RC[10]&RC[11]"

'copy/paste values of column B would go in here

Columns("K:N").Select
Selection.Delete Shift:=xlToLeft

I'm sure I've left out some vital part of all this but I'll definitely check back soon. Thanks for any effort.

Henry

 

[hg1027]

Well I've realized I can sort by name after inserting the rows and get all the blanks at the top, which might make a fill a little easier.