EXCEL MACROS; Clear, Compare and Replace!

Closed
Report
-
 OilCan -
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 :)

8 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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
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 :)
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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.
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?
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
"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
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?
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
are you having only one row.
post your sheet (small extract)
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
Sorry, try this link: http://www.2shared.com/document/KlKP1uhn/Test2.html