VBA check for negative numbers in column and add text in corresponding cell in a different column

[tropics123]

Hi, please help. This macro is supposed to check in column Z to find any negative numbers. If it finds a negative number in a cell, then in the corresponding cell (same row) in column I, it's supposed to put in a message "Applied payment". However, it gets stuck on the line I highlighted in red. Thank you for your help!

Sheets("MAIN").Activate
Dim rng As Range
Set rng = Range("Z1:Z")
For Each cell In rng
If cell.Value < 0 Then
cell.Offset(0, 17).Value = "Applied payment"
End If
Next
End Sub

 

[Fluff]

It's the line above that causes the problem, try

Dim rng As Range
Set rng = Range("Z1", Range("Z" & Rows.Count).End(xlUp))
For Each cell In rng
   If cell.Value < 0 Then
      cell.Offset(0, 17).Value = "Applied payment"
   End If
Next

 

[Mentor82]

Hi,
It is because you use ambigous name cell in for each statement. Cell stands for cell object and cannot be used as a variable object. Here you have fixed code. I also fixed the kine where you return "applied payment".
Check it out and let me know if that works for you.

Sub NegativeNumbers()
Dim rng As
Dim rCell as range
Sheets("MAIN").Activate

Set rng = Range("Z1:Z")
For Each rCell In rng
If rCell.Value < 0 Then
Cells(rCell.row,"I").value="Applied payment"
End If
Next
Set rng=nothing
End Sub

 

[Rick Rothstein]

It is because you use ambigous name cell in for each statement. Cell stands for cell object and cannot be used as a variable object.

That is not correct... Cell is a perfectly valid name to use for a variable (I use it all the time)... it is Cells (with an "s" on the end) that is the object you were referring to.

 

[Mentor82]

Hi,
In the previous post I made a mistake. I've corrected the range line. Check this out.

Sub NegativeNumbers()
Dim rng As
Dim rCell as range
Sheets("MAIN").Activate

Set rng = Range("Z1:" & Range("Z" & rows.count).end(xlup))
For Each rCell In rng
If rCell.Value < 0 Then
Cells(rCell.row,"I").value="Applied payment"
End If
Next
Set rng=nothing
End Sub

 

[Rick Rothstein]

Give this macro a try (it uses a different, non-looping approach)...

Sub AppliedPayments()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "Z").End(xlUp).Row
  Range("I1:I" & LastRow) = Evaluate("IF(Z1:Z" & LastRow & "<0,""Applied Payment"",I1:I" & LastRow & ")")
End Sub

 

[tropics123]

Hi Fluff, I tried your code and it error out on the line I highlighted in red.
Dim rng As Range
Set rng = Range("Z1", Range("Z" & Rows.Count).End(xlUp))
For Each cell In rng
If cell.Value < 0 Then
cell.Offset(0, 17).Value = "Applied payment"
End If
Next

 

[tropics123]

Hi Mentor 82, I tried your code and did you mean "Dim rng As range"? I changed to that and it got stuck on rng = . I highlighted in red.

Sub NegativeNumbers()
Dim rng As
Dim rCell as range
Sheets("MAIN").Activate

Set rng = Range("Z1:" & Range("Z" & rows.count).end(xlup))
For Each rCell In rng
If rCell.Value < 0 Then
Cells(rCell.row,"I").value="Applied payment"
End If
Next
Set rng=nothing
End Sub

 

[Fluff]

What is the error number & message?

 

[Fluff]

Also didn't notice you want to put the text in col I so this

cell.Offset(0, 17).Value = "Applied payment"

should be

cell.Offset(0, [COLOR=#ff0000]-15[/COLOR]).Value = "Applied payment"