EXCEL MACROS; Clear, Compare and Replace!Closed

Question

Hi,    

I need help creating three Excel macro subroutines.... This is for office 2003!   

The first one clears cells that do not contain "arg1" and "arg2", but do not affect the entire row.    

eks.    

arg1 # keep    
arg3 # clear cell    
arg2 # keep    
arg1 # keep    
arg3 # clear cell    
arg4 # clear cell    
arg3 # clear cell    
arg2 # keep    
arg4 # clear cell 

I also need a macro that compares two columns "col1" and "col2", if they are equal, do nothing. If they do not match replace "col1" with the corresponding value of "col2".    
     
eks.    

col1                                       
1                                          
5  # replace with 9                  
7                                                   
3                                            
0 # replace with 1                   
5                                          
2 # replace with 7                

col2  
1  
9  
7  
3  
1  
5  
7  





Note: column2, will contain both digits and letters in this form, "7896 blablabla"    


At the end  I also need a macro that will insert the value 1, in "column A" if  "column B" is NOT empty. This insert will not apply for the entire "column A", only from where "column C" is equal to 1000.    




Thanks a lot :)

venkat1926 · Answer

question 1 data is in sheet 1 from A1 down and to column B . no column heding
try this macro

Sub clearcell()

'note no column heading. data starts from A1. Modify if necessary
Worksheets("sheet1").Activate
Set r = Range(Range("A1"), Range("A1").End(xlDown))
For Each c In r

If Trim(c) <> "arg1" And Trim(c) <> "arg2" Then c.Clear
Next c
End Sub

second question

in sheet 2 column heading in row 1 and data from row 2 onlyl

try this macro

Sub replcenumber()
Worksheets("sheet2").Activate
Set r = Range(Range("B2"), Cells(Rows.Count, "A").End(xlUp).Offset(0, 1))
For Each c In r
If c <> "" Then
c = Trim(c)
j = Len(c)
'MsgBox j
For k = 1 To j
If IsNumeric(Mid(c, k, 1)) Then m = m & Mid(c, k, 1)
Next k
MsgBox m
c.Offset(0, -1) = m
End If
m = ""
Next c
End Sub

third question not clear give an example

OilCan · Answer

Hi,


The first macro works like a charm!! Thank you :)

For the second one, I cannot get it to work..
This is how I edited the code:



Sub replaceNumber()
Set r = Range(Range("Q4"), Cells(Rows.Count, "T").End(xlUp).Offset(0, 1))
For Each c In r
If c <> "" Then
c = Trim(c)
j = Len(c)
'MsgBox j
For k = 1 To j
If IsNumeric(Mid(c, k, 1)) Then m = m & Mid(c, k, 1)
Next k
MsgBox m
c.Offset(0, -1) = m
End If
m = ""
Next c
End Sub


Just to be clear, I would like to compare column Q with column T, if column Q does not have the same value as column T or is blank, replace/fill in the value of column T into column Q.

I seems like I keep getting an error in the "c.Offset(0, -1) = m" - part, or I just might have filled in the range incorrect?

For the third Macro, I'll try my best to explain ;)
The value 1 should be filled in in coulumn S if the correspondig column Q is <=1000 (equal or greater). 

Hope that made a bit more sence :)

venkat1926 · Answer

reg second macro I understand.
I assumed the second column (col T ) will have a string with a number that is 
"replace with  (some number".  perhaps the second column has only a number. 
can you please post a very small extract of column Q and T (with col  R and S for continuity sake).perhaps no macro is necessary. a small formula will do.

reg third question - here also no need for a macro 
in S2 ( presume row 1 is having column headings) copy this formula 
=if(Q2<=1000,1,S2)  here I presume if q value is NOT <=1000 original S will remain. if needed you modify the formula 
copy S2 down.

easiest value of copying  S2 down is 
take the cursor to the right bottom of S2-the cursor become a plus sign(+).click that +sign S2 will be copied as long as data is there in the previous columns(R).
do some experiments on this.

OilCan · Answer

Thank You, It seams to work now! :)

About the Sub clearcell(), is it posible to modify this so that it will delete the entire row correspondig to the cell not containing arg1 or arg2, instead of just clearing it out?

venkat1926 · Answer

"About the Sub clearcell(), is it possible to modify this so that it will delete the entire row corresponding to the cell not containing arg1 or arg2, instead of just clearing it out?"

try this NOT ON ORIGINAL FILE BUT ON A COPY OF THE FILE SO THAT ORIGINAL DATA IS NOT DISTURBED.

Sub clearcell()
Dim r As Range, c As Range
'note no column heading. data starts from A1. Modify if necessary
Worksheets("sheet1").Activate
Set r = Range(Range("A1"), Range("A1").End(xlDown))
For Each c In r

If Trim(c) <> "arg1" And Trim(c) <> "arg2" Then c.EntireRow.Delete
Next c
End Sub

OilCan · Answer

It performs removing of the rows, but it hangs after the removing, in something that seams like an enternal loop? Is there a way to stop it after execution?

venkat1926 · Answer

are you having only one row.  
post your sheet (small extract)

OilCan · Answer

Sure I am using several rows, It performes the removing of rows not containing ARG1 and ARG2 very well, the problem is that it does not stop after the last non-emty row. 

I might be that it runs through all the 65535 rows, as the range is set to?

her is a very simplified copy of the sheet, try for yourself!

http://www.2shared.com/document/M53UZPOC/Test.html

EXCEL MACROS; Clear, Compare and Replace!

8 responses

Similar discussions

Subscribe To Our Newsletter!