Place a Zero in Cell if another cell is equal to or greater another..

Hello,

I am working on a macro and I have one step to complete. I need the macro to go to sheet2 for example.

Look at Column H if equal to or greater than Column M (Both are dates and values will change daily)

If the statement is true I need it to go back to Column J of the same row and enter a 0 (Zero) for the value.

Sounds simple but I can not seem to figure it out. What I have is below but it is not working for the life of me. I need to be able to do this in VBA, the code snipet needs to be dynamic to flex as the number of rows changes daily can be 5 rows or 500+ rows it needs to loop through.

Any ideas on how I can do this the easiest way?
Sub test()
With ActiveSheet
        Firstrow = .UsedRange.Cells(1).Row
        LastR = .UsedRange.Rows(.UsedRange.Rows.Count).Row
        For Lrow = LastR To Firstrow Step -1
            With .Cells(Lrow, "A")
                If Not IsError(.Value) Then
                    If Lrow > 1 Then
                        If .Cells.Offset(0, 8).Value = Cells.Offset(0, 13).Value Then
                            .Cells.Offset(0, 10).Delete
End If
End If
End If
End With
Next Lrow
End With



End Sub

Open in new window

example-data.xls
dn920Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
answer_dudeConnect With a Mentor Commented:
Just get rid of the offsets; try this
Sub test()
    Dim FirstRow, LastR, Lrow
    
    With ActiveSheet
            FirstRow = .UsedRange.Cells(1).Row
            'LastR = .UsedRange.Rows(.UsedRange.Rows.Count).Row
            
            If WorksheetFunction.CountA(Cells) > 0 Then
                'Search for any entry, by searching backwards by Rows.
                LastR = Cells.Find(What:="*", After:=[A1], _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious).Row
            End If

            
            For Lrow = LastR To FirstRow Step -1
                If Not IsError(.Cells(Lrow, "A").Value) Then
                    If Lrow > 1 Then
                        If .Cells(Lrow, 8).Value >= Cells(Lrow, 13).Value Then
                            .Cells(Lrow, 10).Value = 0
                        End If
                    End If
                End If
            Next Lrow
    End With

End Sub

Open in new window

0
 
patrickabCommented:
dn920,

Why do it in VBA when it can be done with a formula like this in column J:

=IF(H2>=M2,0,I2)

and copy down to the end of the data.

Patrick
0
 
jo_mConnect With a Mentor Commented:


hello marcus

you had two probs  one your dates  had'nt been formatted as dates and
second,
 the col next to  j had  a reference formula so by setting the value  of  j to "" you formual didn't bomb  and the  calculation for K  re adjusted

hope this is what you want  
I've attached a file for you  with a copy of sheet 1 in sheet 2   so you can test it
tx
jo




Sub test()
Sheets("sheet1").Select
With ActiveSheet
        Firstrow = .UsedRange.Cells(1).Row + 1
        LastR = .UsedRange.Rows(.UsedRange.Rows.Count).Row
        For Lrow = LastR To Firstrow Step -1
            With .Cells(Lrow, "A")
                If Not IsError(.Value) Then
                    If Lrow > 1 Then
                        If CDate(Cells(Lrow, "H")) >= CDate(Cells(Lrow, "M")) Then
                        Cells(Lrow, "J").Value = ""
                        End If
                        
End If
End If

End With
Next Lrow
End With



End Sub

Open in new window

example-data2.xls
0
 
dn920Author Commented:
Both solutions worked thank you very much for your assistance!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.