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

tropics123

Board Regular
Joined
May 11, 2016
Messages
85
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
It's the line above that causes the problem, try
Code:
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
 
Upvote 0
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.
Code:
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
 
Upvote 0
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.
 
Upvote 0
Hi,
In the previous post I made a mistake. I've corrected the range line. Check this out.
Code:
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
 
Upvote 0
Give this macro a try (it uses a different, non-looping approach)...
Code:
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
What is the error number & message?
 
Upvote 0
Also didn't notice you want to put the text in col I so this
Code:
cell.Offset(0, 17).Value = "Applied payment"
should be
Code:
cell.Offset(0, [COLOR=#ff0000]-15[/COLOR]).Value = "Applied payment"
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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