[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
Solved

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

Posted on 2011-02-17
Medium Priority
290 Views
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
``````
example-data.xls
0
Question by:dn920
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 45

Expert Comment

ID: 34921957
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

LVL 10

Accepted Solution

ID: 34921963
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
``````
0

LVL 2

Assisted Solution

jo_m earned 1000 total points
ID: 34922000

hello marcus

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
``````
example-data2.xls
0

Author Closing Comment

ID: 34922263
Both solutions worked thank you very much for your assistance!!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
###### Suggested Courses
Course of the Month13 days, 8 hours left to enroll