Tweak to VBA code to include last line

[neilp]

What am I doing wrong.

I'm using the Macro below to : find the word Yes in column G, then insert a row beneath that row and fill in column B with the words in the line above (+ the word royalty) . it then copied the number that is in column D and finally copies the formulas, so that they fill in columns A, E and F.

This works great until it gets to the last line. although it copies the number in column D, it does not fill in the formulas and stops at the line above it.

any ideas?

Sub InsertRowAndDragFormulas()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim cellText As String
    Dim startRow As Long

    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    
    startRow = 13
    
    
    lastRow = ws.Cells(ws.Rows.Count, "G").End(xlUp).Row
    
    
    For i = lastRow To startRow Step -1
        If ws.Cells(i, "G").Value = "Yes" Then
            
            cellText = ws.Cells(i, "B").Value
            
            
            ws.Rows(i + 1).Insert Shift:=xlDown
            
            
            ws.Cells(i + 1, "D").Value = ws.Cells(i, "D").Value
            
            
            ws.Cells(i + 1, "B").Value = cellText & " Royalty"
        End If
    Next i
    
    
    Dim formulaColumns As Variant
    formulaColumns = Array("A", "E", "F")
    
    For i = LBound(formulaColumns) To UBound(formulaColumns)
        Dim col As String
        col = formulaColumns(i)
        
        
        Dim lastFormulaRow As Long
        lastFormulaRow = ws.Cells(ws.Rows.Count, col).End(xlUp).Row
        
        
        ws.Range(ws.Cells(startRow, col), ws.Cells(lastFormulaRow, col)).FillDown
    Next i
    
    MsgBox "Macro completed successfully!", vbInformation
End Sub

 

[neilp]

Wk 5 v3.xlsm
	A	B	C	D	E	F	G
22	EO25144BGY	BIDENS GIANT YELLOW	144	2	£ 26.04	£ 52.08	Yes
23	R25BGY	BIDENS GIANT YELLOW Royalty		2	£ 5.15	£ 10.30
24	EO25144BBE	BIDENS BLAZING EMBERS	144	6	£ 28.08	£ 168.48	Yes
25	R25BBE	BIDENS BLAZING EMBERS Royalty		6	£ 7.19	£ 43.16
26	EO25144BBF	BIDENS BLAZING FIRE	144	8	£ 28.08	£ 224.64	Yes
27	R25BBF	BIDENS BLAZING FIRE Royalty		8	£ 7.19	£ 57.54
28	EO25144BBS	BIDENS BLAZING STAR	144	1	£ 28.08	£ 28.08	Yes
29	R25BBS	BIDENS BLAZING STAR Royalty		1	£ 7.19	£ 7.19
30	EO25144BPP	BIDENS PRETTY IN PINK	144	2	£ 28.08	£ 56.16	Yes
31		BIDENS PRETTY IN PINK Royalty		2
32
Sheet1

Cell Formulas
Range	Formula
E22:E30	E22	=VLOOKUP(B22,EOCODES,3,FALSE)
F22:F30	F22	=E22*D22
A22:A30	A22	=VLOOKUP(B22,EOCODES,2,FALSE)

Named Ranges
Name	Refers To	Cells
codes	='Product Sales Prices'!$C$2:$D$350	A22:A30, E22:E30
EOCODES	='Product Sales Prices'!$B$2:$D$500	A22:A30, E22:E30

 

[Joe4]

Maybe try using column B instead of column G to find the last row of data, since column G does not always have an entry, i.e.
instead of:

    lastRow = ws.Cells(ws.Rows.Count, "G").End(xlUp).Row

try:

    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

 

[neilp]

Hi Joe.
thanks for your input
That still brings up the same issue - no foumulas dragged into the last line
thanks
Neil

 

[Joe4]

Please show me the following:
1. What your data looks like to start, before running anything.
2. What it currently looks like after the code runs.
3. What it SHOULD look like, after the code runs.

 

[neilp]

Hi Joe,
your suggestion got me to relook at the code. I decided to try and get it to recalculate the last row after it had done all the inserting of rows.
I added

lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

after the bulk of the code and it is working perfectly now.

thanks for the help

Neil

 

[Joe4]

You are welcome.
Glad I was able to help!