»
 

Go Back   ResellerRatings Store Ratings > ResellerRatings Forums > Tech Support

Reply
 
LinkBack Thread Tools Display Modes
Old 10-02-2003, 10:00 AM   #1 (permalink)
Registered User
 
Join Date: Jun 2003
Posts: 31
jjst34 is on a distinguished road
Excel Macro help

Hi,,
I am trying to write a line of code to do the following. Search in entire column A:A and look for ER600L and if it finds it to delete that entire row along w/ the next 8 rows. So far all I can do is make it highlight the row if it is the active cell w/ this code..

Sub processcells()
If ActiveCell = "ER600L" Then ActiveCell.EntireRow.Select
End Sub

It obviously needs some work and I can't get much further just yet... Thanks for any help!

jjst34 is offline   Reply With Quote
Old 10-02-2003, 10:01 AM   #2 (permalink)
Banned
 
shahani's Avatar
 
Join Date: Oct 2001
Location: Fort Lee, NJ
Posts: 3,417
shahani is on a distinguished road
Irn Duke should be around soon. He knows the answer.
shahani is offline   Reply With Quote
Old 10-02-2003, 10:48 AM   #3 (permalink)
Registered User
 
Join Date: Jun 2003
Posts: 31
jjst34 is on a distinguished road
..
jjst34 is offline   Reply With Quote
Old 10-02-2003, 10:48 AM   #4 (permalink)
Registered User
 
Join Date: Jun 2003
Posts: 31
jjst34 is on a distinguished road
Thanks a lot!!

Got one more if you guys don't mind.. I have a function that says the following:
=IF(LEFT($G14,5)="Total",SUM(I3:I13),"")

Is there a way to replace the above SUM(I3:I13) w/ something like a SUM(from cell just above this : to the final number in the range above)?? Sort of need it to do a ctrl+shift+up twice.

the first function works fine,, but the next time it finds "Total" say, in G55 this time, the #'s I need it to add are more than just the 11 columns the first function SUMS, so now I only get the total of I43:I54, but may need all the way up to I30.. Get what I'm looking for?

Last edited by jjst34; 10-02-2003 at 11:55 AM.
jjst34 is offline   Reply With Quote
Old 10-02-2003, 03:39 PM   #5 (permalink)
Registered User
 
Epidemic's Avatar
 
Join Date: Mar 2002
Posts: 400
Epidemic is on a distinguished road
Code:
Sub biteme()
    Cells(1, 1).EntireColumn.Find("hello", LookIn:=xlWhole, Lookat:=xlWhole).Select
    Selection.EntireRow.Delete
End Sub
Well this is not exactly what i desired but it is close.

of course substitute you test value and column.

Cells (1,1) is the first cell first column then I use entire column to look through the all rows.
Epidemic is offline   Reply With Quote
Old 10-02-2003, 03:54 PM   #6 (permalink)
Registered User
 
Join Date: Jun 2003
Posts: 31
jjst34 is on a distinguished road
Thanks.. This is close.. I need it to delete the following 9 rows also, not just the one w/ "hello" and also, how do I make it loop? It only finds one "hello" then I have to run it again.. Thanks a lot.. Any idea on the SUM question? Thanks guys!
jjst34 is offline   Reply With Quote
Old 10-02-2003, 04:04 PM   #7 (permalink)
Registered User
 
Epidemic's Avatar
 
Join Date: Mar 2002
Posts: 400
Epidemic is on a distinguished road
=IF(G14="Total",SUM(OFFSET(E7,-4,4):OFFSET(E7,6,4)),"")

Not sure if this does what you want but here give this a whirl.

ps this is a formula of course.

I did not generate a macro for this one.

Last edited by Epidemic; 10-02-2003 at 04:17 PM.
Epidemic is offline   Reply With Quote
Old 10-02-2003, 04:15 PM   #8 (permalink)
Registered User
 
Epidemic's Avatar
 
Join Date: Mar 2002
Posts: 400
Epidemic is on a distinguished road
Quote:
Originally posted by jjst34
Thanks.. This is close.. I need it to delete the following 9 rows also, not just the one w/ "hello" and also, how do I make it loop? It only finds one "hello" then I have to run it again.. Thanks a lot.. Any idea on the SUM question? Thanks guys!
you can put that directly in a loop as far as I am concerned

Perhaps a do while loop.

Code:
Sub biteme()
    On Error GoTo done
    Do While a < 1000
    Cells(1, 1).EntireColumn.Find("hello", LookIn:=xlWhole, Lookat:=xlWhole).Select
    Range(ActiveCell(), ActiveCell.Offset(-9, 0)).Select
    Selection.EntireRow.Delete
    a = 1 'this is just a bogus variable
    Loop
done:
End Sub
Epidemic is offline   Reply With Quote
Old 10-02-2003, 04:40 PM   #9 (permalink)
Registered User
 
Epidemic's Avatar
 
Join Date: Mar 2002
Posts: 400
Epidemic is on a distinguished road
I put in the bogus variable because I could not think of what to loop until. I really just wanted to loop until there was an error then break out.

If the find finds nothing then you get an error and on that error you go to done:

I also notice that my -9 probably should be positive if you want to select the next 9 rows.



Last edited by Epidemic; 10-02-2003 at 04:44 PM.
Epidemic is offline   Reply With Quote
Old 10-03-2003, 07:12 AM   #10 (permalink)
Registered User
 
Join Date: Jun 2003
Posts: 31
jjst34 is on a distinguished road
Here is what I get when I run it... Not sure how to fix it...
Attached Images
File Type: jpg untitled.jpg (70.8 KB, 34 views)
jjst34 is offline   Reply With Quote
Reply




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Most Active Discussions

Recent Discussions

All times are GMT -6. The time now is 07:20 AM.