VBA not copying cell value when running it on certain files

[zack8576]

I have this code that counts the number of "CHAIN & EYEBOLT" in some excel files, then adds one row at the end of the file to show the total number of this item.
The new row should always have: column A (value from cell above), C (total number of the item), B, D, I and K with the static values shown below

This code works............on 90% of the files, but on a few test files, it is not copying the value in column A, like the example below, cell A returned as blank
I am not sure exactly what is causing this issue, any help is appreciated

dropbox link below for 2 test files, this one is working

NO EYEBOLT AND CHAIN ISSUE.csv

Shared with Dropbox

www.dropbox.com

and this one is not

EYEBOLT AND CHAIN ISSUE.csv

Shared with Dropbox

www.dropbox.com

full code below, any criticism on the way this is coded is also welcome.
I am always looking for ways to improve these VBA codes..

Sub EyeboltsAndChains()
    Dim lrNew As Long
    lrNew = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    sr = 2
    With Range("M1", Cells(Rows.Count, "M").End(3))
            .Replace What:="@ 18""", Replacement:=vbNullString, LookAt:=xlPart
            .Replace What:="@ 24""", Replacement:=vbNullString, LookAt:=xlPart
            .Replace What:="@ 30""", Replacement:=vbNullString, LookAt:=xlPart
            .Replace What:="@ 42""", Replacement:=vbNullString, LookAt:=xlPart
            .Replace What:="@ 48""", Replacement:=vbNullString, LookAt:=xlPart
            .Replace What:="@ 60""", Replacement:=vbNullString, LookAt:=xlPart
            .Replace What:="@ 72""", Replacement:=vbNullString, LookAt:=xlPart
            .Replace What:="&", Replacement:=vbNullString, LookAt:=xlPart
            .Replace What:="2 1", Replacement:="3", LookAt:=xlPart
    End With
    
    n = WorksheetFunction.SumIfs(Range("M" & sr & ":M" & lr), Range("K" & sr & ":K" & lr), "*CHAIN & EYEBOLT*")
    lrNew = lrNew + 1

    Cells(lrNew, "A") = Cells(lr, "A")
    Cells(lrNew, "B") = "."
    Cells(lrNew, "C") = n
    Cells(lrNew, "D") = "F09720"
    Cells(lrNew, "I") = "Purchased"
    Cells(lrNew, "K") = "CHAIN & EYEBOLT"

    If Cells(lrNew, "C").Value Like "*0*" Then
        Rows(lrNew).Delete
    End If
End Sub

 

[Legacy 143009]

The issue is because actually you have mixed up use of lr and lrNew so much. If I were you, at first, I would declare both properly:

Dim lrNew As Long, lr As Long

Then use only lr as last row until the end of the code:

lr = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

At this point I would do a move like this:

lrNew = lr + 1

I think the code must run fine after all these fixes.

 

[zack8576]

The issue is because actually you have mixed up use of lr and lrNew so much. If I were you, at first, I would declare both properly:

Dim lrNew As Long, lr As Long

Then use only lr as last row until the end of the code:

lr = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

At this point I would do a move like this:

lrNew = lr + 1

I think the code must run fine after all these fixes.

thanks, this indeed fixed the issue.