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

 

[VBE313]

have you try to put them on separate lines after each comma? Also remove the period after .Cells for the first two .Cells.

(.Cells.(j, "H") 'This cant happen'
(.Cells(j, "H") 'It should be like this'

 

[Johnny Thunder]

I feel like I am getting closer but still no luck. Here is what I got but it is still erroring out.

Error Msg: "Wrong number of arguments or invalid property assignment"

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

 

[VBE313]

How about

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

Im not sure what you are trying to do with this line.

 

[Michael M]

Maybe this

 .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

But why have the offsets

 .Range(Cells(j+1, "B"),Cells(j+1, "W")).Value = Cells(j, "B"),.Cells(j, "W")).Value 'Issue Line

 

[Johnny Thunder]

The line is intended to define the Row below the row that the loop is on with the same values.

Essentially, a copy of paste or Row 1 to Row 2 within the loop. (1 & 2 are just an example of where to loop could be)

 

[Johnny Thunder]

Maybe this

 .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

Michael, thank you for stepping in on this. Unfortunately, the proposed code errors out (Error 438: Object doesnt support this property or method)

 

[Michael M]

OK, maybe this

.Range(Cells(j + 1, "B"), Cells(j + 1, "W")).Value = .Range(Cells(j, "B"), Cells(j, "W")).Value

AND
I'm guessing this line

.Cells(j, "H").Offset(1, 0).Value = -Cell.Value

should be

.Cells(j, "H").Offset(1, 0).Value = Cell.Value*-1

 

[Johnny Thunder]

Unfortunately, the code didn't work either. I am getting a "Error 91: Object Variable or with block variable not set" and its clearly set so not sure why its erroring out.

Here is the 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 + 1, "A"), Cells(j + 1, "J")).Value = .Range(Cells(j, "A"), Cells(j, "J")).Value
                    .Cells(j, "H").Offset(1, -6).Value = "40"
                    .Cells(j, "H").Offset(1, 0).Value = -.Cells(j, "H").Offset(-1, 0).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 If
            End With
        Next j

Any ideas @Fluff, @Peter_SSs, @Joe4, @Norie

Just some further details: The code is intended to loop thru a Financial Journal Entry, in the event of a negative amount in Column H, the code is to tag that line in Column A with a "50" and then create a duplicate record underneath that row and tag that column A with "40" and create an opposite $Amount hence the "-.Cells(j, "H").Offset(-1, 0).Value". Hopefully this makes sense. It was working with my original Code in the first post but I noticed as the report grew in length the "LastRow" wasn't being followed since the code was adding in some many new rows. Any ideas are appreciated.

 

[CSmith]

I feel like I am getting closer but still no luck. Here is what I got but it is still erroring out.

Error Msg: "Wrong number of arguments or invalid property assignment"

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

Maybe:

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