[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Macro to search and replace values >0 and replace with 0

Posted on 2011-10-07
10
Medium Priority
?
361 Views
Last Modified: 2012-05-12
Hi,
I am trying to modify some vba code I found here to replace all the values in a range that are  greater than zero back to zero. I am going to start with a range selected and then run the code on the range.
So far I have:

Sub nozeros()
    Dim rng1 As Range
    Dim lngRow As Long
    Dim lngCol As Long
    Dim X()
   
 With Selection
 Set rng1 = Selection
    'If rng1 Is Nothing Then Exit Sub
    On Error GoTo 0
   
        If rng1.Column > 0 Then
   
             X = rng1.Value2
             
           
            For lngRow = 1 To rng1.Rows.Count
                For lngCol = 1 To rng1.Columns.Count
                If X(lngRow, lngCol) > 0 Then X(lngRow, lngCol) = 0
                Next lngCol
            Next lngRow
            rng1.Value2 = X
       End If
      End With
     
    End Sub
 
I can't get past the type mismatch error on the   X = rng1.Value2

Thanks,

Bruce




Original code found here.
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_2684-Using-varinat-arrays-in-VBA-for-large-scale-data.html
0
Comment
Question by:Diaphanosoma
  • 5
  • 3
  • 2
10 Comments
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 1600 total points
ID: 36933745
Try this - your type mismatch may have been because of some error values in you data - this should be more robust:
Sub nozeros()
    Dim rng1 As Range
    Dim lngRow As Long
    Dim lngCol As Long
    Dim X As Variant
    
    Set rng1 = Selection
    'If rng1 Is Nothing Then Exit Sub
    On Error GoTo 0
    
    If rng1.Cells.Count > 1 Then

         X = rng1.Value2
         
        For lngRow = 1 To rng1.Rows.Count
            For lngCol = 1 To rng1.Columns.Count
                If Not IsError(X(lngRow, lngCol)) Then
                    If X(lngRow, lngCol) > 0 Then X(lngRow, lngCol) = 0
                End If
            Next lngCol
        Next lngRow
        rng1.FormulaR1C1 = X
    Else
        If Not IsError(rng1.Value2) Then
            If rng1.Value > 0 Then rng1.Value2 = 0
        End If
    End If
      
End Sub

Open in new window

0
 
LVL 2

Assisted Solution

by:geoffkk
geoffkk earned 400 total points
ID: 36934356
If all you want to do is replace values above zero with zero, try this:
Sub test()
Dim c As Range
For Each c In Selection
If c.Value > 0 Then c.Formula = 0
Next c
End Sub

Keep it simple
Geoff
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36935362
geoffkk - I agree with keeping it simple.  I was fixing the immediate problem  with Bruce's code, which is that it needed error handling to manage error values in the range - your code still falls over if there is say a #DIV/0! error in a cell.

The other reason for doing it the way Bruce did is that for larger ranges of data where you need to look at every value in the range, it is much faster to get all the data into a variant array and process it there, then replace it at the end.  A lot of the elapsed time in VBA can be spent just transferring data backwards and forwards between VBA and the grid.

So I would advise Bruce to do it as I suggested for larger ranges, but to use your version with the addition of an IFERROR condition for small ranges.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 17

Expert Comment

by:andrewssd3
ID: 36941337
In fact writing that last answer prompted me to do some performance tests to find how many cells you need to be dealing with to make the array solution more efficient than the For Each.  The answer seems to be 2!  For Each is marginally quicker with one cell, but much slower for any more.  Of course this does not mean you shouldn't use the simpler method if performance is not important, but it is interesting to performance geeks like me.  I think I may try to put together a brief article summarising the timings for various types of loop in VBA.
0
 
LVL 2

Expert Comment

by:geoffkk
ID: 36941618
The timing impact is very interesting, something we usually don't think about. I have sometimes found things taking a long time and put in debug statements to print timer() etc at various points in code and it is sometimes surprising where the delays occur. One should also look at native worksheet functions that are sometimes faster than looping through code.
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36941705
Sure - don't get me wrong, I'm not at all saying that performance is a consideration in all but a few cases.  In the lower numbers of cells we're only talking about timings in the hundredths of seconds - so the difference between 0.01 and 0.03 seconds is not significant for the vast majority of cases.  It only gets important at much higher numbers of iterations.  I totally agree about worksheet functions - they're almost always faster if there's one that does the job.
0
 
LVL 1

Author Comment

by:Diaphanosoma
ID: 36948361
Hey Guys,

I will check out the simple answer a little later today. I forgot it was Thanksgiving in Canada this weekend so I'm behind on everything.

Bruce
0
 
LVL 1

Author Comment

by:Diaphanosoma
ID: 36955778
Thanks for the responses. I just have one question regarding the line
  rng1.FormulaR1C1 = X that follows the loop. Why is this necessary?
Or rather, why couldn't values be assigned directly within the loop?
Something like if rng1.FormulaR1C1 >0 then rng1.FormulaR1C1 =0.

Thanks,

Bruce  
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36955880
That line assigns the values you have been manipulating in the array back into the grid.  Yes you could assign the values direct to the grid in the loop as you say, but that would almost certainly be slower, unless you were expecting to update a very small proportion of the values.
0
 
LVL 1

Author Closing Comment

by:Diaphanosoma
ID: 36955917
Thanks for all the answers and the explanation.

Bruce
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

834 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