VBA Help - Rewrite a For Each Statement

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
693
Office Version
  1. 2016
Platform
  1. 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:
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
have you try to put them on separate lines after each comma? Also remove the period after .Cells for the first two .Cells.
VBA Code:
(.Cells.(j, "H") 'This cant happen'
(.Cells(j, "H") 'It should be like this'
 
Upvote 0
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"

Code:
.Range(.Cells(j + 1, "A"), .Cells(j + 1, "J").Value = .Cells(j, "A"), .Cells(j, "J")).Value 'Issue Line
 
Upvote 0
How about
VBA Code:
.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.
 
Upvote 0
Maybe this
VBA Code:
 .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
VBA Code:
 .Range(Cells(j+1, "B"),Cells(j+1, "W")).Value = Cells(j, "B"),.Cells(j, "W")).Value 'Issue Line
 
Upvote 0
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)
 
Upvote 0
Maybe this
VBA Code:
 .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)
 
Upvote 0
OK, maybe this
VBA Code:
.Range(Cells(j + 1, "B"), Cells(j + 1, "W")).Value = .Range(Cells(j, "B"), Cells(j, "W")).Value
AND
I'm guessing this line
VBA Code:
.Cells(j, "H").Offset(1, 0).Value = -Cell.Value

should be

VBA Code:
.Cells(j, "H").Offset(1, 0).Value = Cell.Value*-1
 
Upvote 0
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:
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.
 
Upvote 0
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"

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

Maybe:
VBA Code:
.Range(.Cells(j + 1, "A"), .Cells(j + 1, "J")).Value = .Range(.Cells(j, "A"), .Cells(j, "J")).Value 'Issue Line
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top