VBA Help - Rewrite a For Each Statement

[Johnny Thunder]

Hello all,

I am working on a project and realized I messed up one piece of code but I was able to spot the problem.

Here is what I have come up with,

I have a For Each statement that scans my data and when there is a negative amount in column ("H2:H" & LastRow) process a few actions + Insert a row under the found negative row. Here is where I noticed my problem, whenever the code inserts a new row, it actually drops my actual LastRow further down then where the code thinks the actual last row is.... So I have worked with this kind of scenario in the past but the method of which I used was a (For i = LastRow to 2 step -1) type of code.

Here is my original code:

For Each Cell In ws1.Range("H2:H" & LastR2)
            If Cell.Value < 0 Then
                Cell.Offset(0, -6).Value = "50"
                Cell.Offset(1, 0).EntireRow.Insert Shift:=xlDown
                ws1.Range(Cell.Offset(1, -6), Cell.Offset(1, 15)).Value = ws1.Range(Cell.Offset(0, -6), Cell.Offset(0, 15)).Value
                Cell.Offset(1, -6).Value = "40"
                Cell.Offset(1, 0).Value = -Cell.Value
                Cell.Offset(1, -4).Formula = "=IFERROR(VLOOKUP(" & Cell.Offset(0, -4).Address & ",'Reclass Mapping'!$D:$H,5,0),""No Mapping"")" 'Reverse Offset Cost Center
                If Cell.Row = 454 Then MsgBox Cell.Row
            End If
        Next Cell

Here is what I was trying to revise it too but I am having a problem on one of the lines (commented with "Issue Line"). Any idea on how to revise either of the codes to get them to start at the bottom and make their way up?

For j = LastR2 To 2 Step -1
           With ws1
            If .Cells(j, "H").Value < 0 Then
                .Cells(j, "H").Offset(0, -6).Value = "50"
                .Cells(j, "H").Offset(1, 0).EntireRow.Insert Shift:=xlDown
               .Range(.Cells.(j,"H").Offset(1, -6), .Cells.(j,"H").Offset(1, 15)).Value = .Cells(j,"H".Cell.Offset(0, -6), .cells(j,"H").Cell.Offset(0, 15)).Value  'Issue Line
                .Cells(j, "H").Offset(1, -6).Value = "40"
                .Cells(j, "H").Offset(1, 0).Value = -Cell.Value
                .Cells(j, "H").Offset(1, -4).Formula = "=IFERROR(VLOOKUP(" & .Cells(j, "H").Offset(0, -4).Address & ",'Reclass Mapping'!$D:$H,5,0),""No Mapping"")"
            End With
        Next j

 

[Johnny Thunder]

That worked out great CSmith!

 

[CSmith]

Most welcome!

Fresh perspective sometimes

 

[Michael M]

@jt
Maybe you need to post ALL the code there may be other instances of lines causing the error

 

[Legacy 456129]

What's the exact error message? Sometimes type mismatches are vexing, so using converters such as CLng(.Cells(j, "J")).Value) ensures that an expected number is really treated as a number. No hope of course if the content is alphanumeric though. Debug Stepping through and hovering with the cursor on the elements of the problematic line will also often reveal which part is the issue. In this case put a bunch of debug.print statements in front and activating the Immediate Window (Ctrl-G) will print out the true value of the references like

Debug.Print .Cells(j, "J").Value

 

[CSmith]

.Range(.Cells(j + 1, "A"), .Cells(j + 1, "J").Value = .Cells(j, "A"), .Cells(j, "J")).Value 'Issue Line

Line has 2 problems:
1. missing ')' before '.Value ='
2. missing '.Range(' after '.Value ='