Excel: Find & OffsetSolved/Closed

Question

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

rizvisa1 · Answer

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

madambath · Answer

SOLVED 

Hi Riz,

Thank you very much. I appreciate the help & support that you always showing to the forum members. 

With regards,
PramodKumar

Excel: Find & Offset

2 responses

Similar discussions

Subscribe To Our Newsletter!