Excel: Find & Offset [Solved/Closed]

madambath - May 28, 2010 at 08:20 AM - Latest reply: rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen
- May 28, 2010 at 02:12 PM
Hi can someone help me to get a macro code to make following:

I have a file in which some cells having data as <0.01, < 0.01, >10000, > 10000 and some others 100001, 120000 etc along with other values (please note: due to data entry problems some cell have "space" between < or > before numbers [ like < 0.01 or < 10000] but some cells does not have space) What I want is find exactly <0.01 or < 0.01 (it should not look for empty cells) then offset 3 columns right and clear content of that cell. Likewise find >10000, > 10000 and any numbers greater than 10000 then offset 4 columns right and clear content of that cell.

A file showing my requirements are uploaded in sppedsahe .com, the download link is: http://www.speedyshare.com/files/22667485/Find_Offset.xls

Thanks in advance,
PramodKumar
See more 

3 replies

rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - May 28, 2010 at 10:34 AM
0
Thank you
See if this help

Sub SearchAndReplace()    
Dim myRange As Range    
Dim Cell As Range    

    Set myRange = Range("E3:Q32")    
        
    For Each Cell In myRange    
        
        If ((Cell = ">0.01") Or (Cell = "> 0.01")) Then    
                
            Cell.Offset(0, 3) = ""    
            
        ElseIf ((Cell = ">10000") Or (Cell = "> 10000")) Then    
                
            Cell.Offset(0, 4) = ""    
            
        ElseIf ((IsNumeric(Cell)) And (Cell >= 10000)) Then    
            
            Cell.Offset(0, 4) = ""    
                
        End If
        
    Next Cell    
        
    Set Cell = Nothing  
    Set myRange = Nothing  
         
End Sub    
0
Thank you
SOLVED

Hi Riz,

Thank you very much. I appreciate the help & support that you always showing to the forum members.

With regards,
PramodKumar
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - May 28, 2010 at 02:12 PM
You are welcome Pramod