Excel: Find & Offset

[Solved/Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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

2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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    
SOLVED

Hi Riz,

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

With regards,
PramodKumar
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
You are welcome Pramod