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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,103
Office Version
  1. 365
Platform
  1. Windows
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
 

Mentor82

Active Member
Joined
Dec 30, 2018
Messages
311
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,691
Office Version
  1. 2010
Platform
  1. Windows
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

Active Member
Joined
Dec 30, 2018
Messages
311
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,691
Office Version
  1. 2010
Platform
  1. Windows
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
 

tropics123

Board Regular
Joined
May 11, 2016
Messages
85

ADVERTISEMENT

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

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,103
Office Version
  1. 365
Platform
  1. Windows
What is the error number & message?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,103
Office Version
  1. 365
Platform
  1. Windows
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"
 

Watch MrExcel Video

Forum statistics

Threads
1,122,884
Messages
5,598,651
Members
414,251
Latest member
oExcel

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
Top