Solved

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

Posted on 2011-02-17
4
282 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:dn920
4 Comments
 
LVL 45

Expert Comment

by:patrickab
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

by:
answer_dude earned 250 total points
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

Open in new window

0
 
LVL 2

Assisted Solution

by:jo_m
jo_m earned 250 total points
ID: 34922000


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
 

Author Closing Comment

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

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now