Johnny Thunder
Well-known Member
- Joined
- Apr 9, 2010
- Messages
- 693
- Office Version
- 2016
- Platform
- MacOS
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:
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?
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:
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?
Code:
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