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

 

[tropics123]

Hi Rick, your macro worked perfectly except one tiny issue. It adds zero value in the cells in column I when there are no negative numbers in column Z. Is there a way to avoid that?

 

[tropics123]

Hi Fluff, the error was "Run-time error '13': Type mismatch"

 

[tropics123]

Hi Fluff, the error was "Run-time error '13': Type mismatch:

 

[Mentor82]

Check now.
@Rick - I admit you're right, my mistake. CELL is a good name for a variable

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

Set rng = Range("Z1:Z" & Range("Z" & rows.count).end(xlup).row)
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]

Hi Rick, your macro worked perfectly except one tiny issue. It adds zero value in the cells in column I when there are no negative numbers in column Z. Is there a way to avoid that?

I assumed you had data in Column I that needed to be preserved. If the only text that will be in that column is the "Applied Payment" that this macro puts there, then you can use this macro instead...

Sub AppliedPayments()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "Z").End(xlUp).Row
  Range("I[B][COLOR="#FF0000"]1[/COLOR][/B]:I" & LastRow) = Evaluate("IF(Z[B][COLOR="#FF0000"]1[/COLOR][/B]:Z" & LastRow & "<0,""Applied Payment"","""")")
End Sub

NOTE: If you have a header in cel I1, then change each red 1 above to a 2.

 

[Fluff]

Hi Fluff, the error was "Run-time error '13': Type mismatch:

That would suggest that you have error values in col Z like #N/A, #VALUE! etc

 

[tropics123]

Thank you Rick! It worked perfectly!!! Have a great weekend!