Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
Good Day,
I am new to writing Macros and my first one worked to the specific data but now that the data has change it is getting caught up and need a little help on how to correct it. The following is where it is getting hung up and I think I need to make it a little more general but not sure where to start. Here is what I have: Do Until ActiveCell.Value = "Consumer Discretionary Total" Set R = Columns("C").find("Consumer Discretionary Total", LookAt:=xlPart, MatchCase:=False) R.Offset(, 1).Select Any help offered is greatly appreciated.. Thank you for your time |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
Do you mean?
strFind = "Consumer Discretionary Total" OR strFind = Range("A1") Set r = Columns("C").Find(strFind, _ LookAt:=xlPart, MatchCase:=False) r.Offset(, 1).Select If this post helps click Yes --------------- Jacob Skaria "wblake0926" wrote: Good Day, I am new to writing Macros and my first one worked to the specific data but now that the data has change it is getting caught up and need a little help on how to correct it. The following is where it is getting hung up and I think I need to make it a little more general but not sure where to start. Here is what I have: Do Until ActiveCell.Value = "Consumer Discretionary Total" Set R = Columns("C").find("Consumer Discretionary Total", LookAt:=xlPart, MatchCase:=False) R.Offset(, 1).Select Any help offered is greatly appreciated.. Thank you for your time |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
Thanks for the quick response, this is difficult being so new, I probably did
an aweful job explaining what I am trying to do this is what my spread sheet has in Column C: Agency ( Agcy ) Agency ( Agcy ) Total Banking ( Bank ) Banking ( Bank ) Banking ( Bank ) Banking ( Bank ) Total Basic Industry ( Basc ) Basic Industry ( Basc ) Basic Industry ( Basc ) Total Brokerage ( Brkg ) Brokerage ( Brkg ) Brokerage ( Brkg ) Total What I would like to do is go down the column and find everything that ends in Total and insert a blank row... I hope this explanation is better, I apologize for being so vague on last post, appreciate the help "Jacob Skaria" wrote: Do you mean? strFind = "Consumer Discretionary Total" OR strFind = Range("A1") Set r = Columns("C").Find(strFind, _ LookAt:=xlPart, MatchCase:=False) r.Offset(, 1).Select If this post helps click Yes --------------- Jacob Skaria "wblake0926" wrote: Good Day, I am new to writing Macros and my first one worked to the specific data but now that the data has change it is getting caught up and need a little help on how to correct it. The following is where it is getting hung up and I think I need to make it a little more general but not sure where to start. Here is what I have: Do Until ActiveCell.Value = "Consumer Discretionary Total" Set R = Columns("C").find("Consumer Discretionary Total", LookAt:=xlPart, MatchCase:=False) R.Offset(, 1).Select Any help offered is greatly appreciated.. Thank you for your time |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
Hi
Try if this is what you need. Sub InsertLineAfterTotal() Dim LastRow As Long Dim r As Long LastRow = Range("C" & Rows.Count).End(xlUp).Row For r = LastRow To 1 Step -1 If InStr(1, Range("C" & r).Value, "Total", vbTextCompare) Then Rows(r + 1).EntireRow.Insert End If Next End Sub Regards, Per On 23 Sep., 21:09, wblake0926 wrote: Thanks for the quick response, this is difficult being so new, I probably did an aweful job explaining what I am trying to do this is what my spread sheet has in Column C: Agency ( Agcy ) Agency ( Agcy ) *Total Banking ( Bank ) Banking ( Bank ) Banking ( Bank ) Banking ( Bank ) *Total Basic Industry ( Basc ) Basic Industry ( Basc ) Basic Industry ( Basc ) *Total Brokerage ( Brkg ) Brokerage ( Brkg ) Brokerage ( Brkg ) *Total What I would like to do is go down the column and find everything that ends in Total and insert a blank row... I hope this explanation is better, I apologize for being so vague on last post, appreciate the help "Jacob Skaria" wrote: Do you mean? strFind = "Consumer Discretionary Total" OR strFind = Range("A1") Set r = Columns("C").Find(strFind, _ LookAt:=xlPart, MatchCase:=False) r.Offset(, 1).Select If this post helps click Yes --------------- Jacob Skaria "wblake0926" wrote: Good Day, I am new to writing Macros and my first one worked to the specific data but now that the data has change it is getting caught up and need a little help on how to correct it. The following is where it is getting hung up and I think I need to make it a little more general but not sure where to start. Here is what I have: Do Until ActiveCell.Value = "Consumer Discretionary Total" *Set R = Columns("C").find("Consumer Discretionary Total", LookAt:=xlPart, MatchCase:=False) * *R.Offset(, 1).Select Any help offered is greatly appreciated.. Thank you for your time- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
Perfect!!
Thanks a lot! This is exactly what I needed... "Per Jessen" wrote: Hi Try if this is what you need. Sub InsertLineAfterTotal() Dim LastRow As Long Dim r As Long LastRow = Range("C" & Rows.Count).End(xlUp).Row For r = LastRow To 1 Step -1 If InStr(1, Range("C" & r).Value, "Total", vbTextCompare) Then Rows(r + 1).EntireRow.Insert End If Next End Sub Regards, Per On 23 Sep., 21:09, wblake0926 wrote: Thanks for the quick response, this is difficult being so new, I probably did an aweful job explaining what I am trying to do this is what my spread sheet has in Column C: Agency ( Agcy ) Agency ( Agcy ) Total Banking ( Bank ) Banking ( Bank ) Banking ( Bank ) Banking ( Bank ) Total Basic Industry ( Basc ) Basic Industry ( Basc ) Basic Industry ( Basc ) Total Brokerage ( Brkg ) Brokerage ( Brkg ) Brokerage ( Brkg ) Total What I would like to do is go down the column and find everything that ends in Total and insert a blank row... I hope this explanation is better, I apologize for being so vague on last post, appreciate the help "Jacob Skaria" wrote: Do you mean? strFind = "Consumer Discretionary Total" OR strFind = Range("A1") Set r = Columns("C").Find(strFind, _ LookAt:=xlPart, MatchCase:=False) r.Offset(, 1).Select If this post helps click Yes --------------- Jacob Skaria "wblake0926" wrote: Good Day, I am new to writing Macros and my first one worked to the specific data but now that the data has change it is getting caught up and need a little help on how to correct it. The following is where it is getting hung up and I think I need to make it a little more general but not sure where to start. Here is what I have: Do Until ActiveCell.Value = "Consumer Discretionary Total" Set R = Columns("C").find("Consumer Discretionary Total", LookAt:=xlPart, MatchCase:=False) R.Offset(, 1).Select Any help offered is greatly appreciated.. Thank you for your time- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AutoRun Macro with a delay to give user the choice to cancel the macro | Excel Programming | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Macro not showing in Tools/Macro/Macros yet show up when I goto VBA editor | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |