Solved

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

Posted on 2011-02-17
4
287 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
[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
  • Learn & ask questions
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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…

738 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