?
Solved

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

Posted on 2011-02-17
4
Medium Priority
?
292 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 1000 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 1000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
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.

850 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