Solved

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

Posted on 2011-02-17
4
284 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

776 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