• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

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

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
Diaphanosoma
Asked:
Diaphanosoma
  • 5
  • 3
  • 2
2 Solutions
 
andrewssd3Commented:
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
 
geoffkkCommented:
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
 
andrewssd3Commented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
andrewssd3Commented:
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
 
geoffkkCommented:
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
 
andrewssd3Commented:
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
 
DiaphanosomaAuthor Commented:
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
 
DiaphanosomaAuthor Commented:
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
 
andrewssd3Commented:
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
 
DiaphanosomaAuthor Commented:
Thanks for all the answers and the explanation.

Bruce
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now