Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding 4 rows after each total criteria in the column A with formatting and added text using macro.
Hello,
I have been working on this project for ages now and I have little more fine tuning to do before it's offically automated friendly. AIM is To add 4 rows after "total" with color and text. Insert row a with text as "Avails" under "Total" and entire row background color as Blue Insert row b with text as "Left" under "Avails" and entire row background color as Yellow Insert row c & d are just empty rows. I would like to do this after every "total" criteria in column A. ie A B C D "" 1 "" 0 "" 2 "" 2 Total 5 row a "Avails" row b "Left" row c row d "" "" "" "" "" "" Total row a "Avails" row b "Left" row c row d |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding 4 rows after each total criteria in the column A with formatting and added text using macro.
Hi
You could try something like this Option Explicit Dim MyCell, MyRng As Range Dim i As Integer Private Sub CommandButton1_Click() Set MyRng = [a1:a100] 'set your range For Each MyCell In MyRng If MyCell.Value = "total" Then MyCell.Offset(1, 0).Select Do While i < 4 ActiveCell.EntireRow.Insert i = i + 1 Loop ActiveCell.Value = "Avails" ActiveCell.EntireRow.Interior.ColorIndex = 5 ActiveCell.Offset(1, 0).Value = "Left" ActiveCell.Offset(1, 0).EntireRow.Interior.ColorIndex = 6 End If Next MyCell End Sub hope it is of some help to you S |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding 4 rows after each total criteria in the column A with formatting and added text using macro.
Hello,
I can see the logic in this and I have attempted a few times this morning to embed the code in a commandbutton - However it's not doing anything for the moment. Does this have to be a commandbutton? Can this be a customized macro button that I can assign it. I populate this several times a week and might be harder to constantly add a command button. On Mar 20, 6:33 am, "Incidental" wrote: Hi You could try something like this Option Explicit Dim MyCell, MyRng As Range Dim i As Integer Private Sub CommandButton1_Click() Set MyRng = [a1:a100] 'set your range For Each MyCell In MyRng If MyCell.Value = "total" Then MyCell.Offset(1, 0).Select Do While i < 4 ActiveCell.EntireRow.Insert i = i + 1 Loop ActiveCell.Value = "Avails" ActiveCell.EntireRow.Interior.ColorIndex = 5 ActiveCell.Offset(1, 0).Value = "Left" ActiveCell.Offset(1, 0).EntireRow.Interior.ColorIndex = 6 End If Next MyCell End Sub hope it is of some help to you S |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding 4 rows after each total criteria in the column A with formatting and added text using macro.
I think it doesn't work because it is specifically looking for "total"
- the total row will have some text. ie 04/02/07 - a b c - Total |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding 4 rows after each total criteria in the column A with formatting and added text using macro.
Hi
I'm not sure exactly what you are wanting to do, from the original description it sounds like you have a cell that appears every so often in the column A with the value of "total"??? if this is the case you can run the code from a button or you can assign a keyboard shortcut to it. you can do the latter by opening the visual basic editor or press Alt and F11 then add a new module then paste the given code into the module just change the line Private Sub CommandButton1_Click() to something like Sub RunMyCode() then in excel from the tools menu select the macro option, from the Form that pops up high light your macro RunMyCode then select the options button and add a shortcut key. then you will be able to run the code from this shortcut. if you don't have the cell value of "total" to mark where you are wanting the changes to be made you will have to search for something that will indicate which cells to run the code from, i.e. if the total is always have 5 cells between you could use that to activate your code. also if you are doing this in different workbooks every time you may have to do something like create a workbook with the code you want to run then add a little bit of code to open the other workbook and then run the code on that workbook as having to add the code every time is very impractical. hope this is somewhat clearer |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding 4 rows after each total criteria in the column A with formatting and added text using macro.
Back again
sorry i forgot to add if you wish to run the code more than once in the same session you should reset the integer i to 0 you can do this by adding the line "i = 0" above end sub S |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding 4 rows after each total criteria in the column A with formatting and added text using macro.
Good Morning S,
I'm getting close. Thank you so much in advance. The code works - However it only works if I change the total header to say "Total". The header may will vary from week to week. Can the code work if it contains the value "Total". Also, the row insertion needs a little tweaking. I've uploaded an image of a better example. ie http://bp1.blogger.com/_0XguicNvp2w/...-h/example.bmp |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding 4 rows after each total criteria in the column A with formatting and added text using macro.
Hi again
I have made it my goal today to get this right lol, anywho I think if you use the code below it should work fine (we can but hope) I changed the conditional statement to check the cells that contain a value to see if the last 5 letters of the string contain either the word Total or total if so then it will do run the code. I also reset the iteration of the I integer as soon as the loop stops which I think was the problem there. Well give it a try see how it works out Option Explicit Dim MyCell, MyRng As Range Dim i As Integer Sub RunMyCode() Set MyRng = [a1:a100] 'Set your range For Each MyCell In MyRng If MyCell.Value = "" Then 'Check that cell has a value If Right(MyCell, 5) = "total" Or Right(MyCell, 5) = "Total" Then 'Find last 5 letters in string MyCell.Offset(1, 0).Select Do While i < 4 ActiveCell.EntireRow.Insert i = i + 1 Loop i = 0 'Reset the counter here ActiveCell.Value = "Avails" ActiveCell.EntireRow.Interior.ColorIndex = 5 ActiveCell.Offset(1, 0).Value = "Left" ActiveCell.Offset(1, 0).EntireRow.Interior.ColorIndex = 6 End If End If Next MyCell End Sub Steve |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding 4 rows after each total criteria in the column A with formatting and added text using macro.
Steve
Thank you so much. If you have an email account would you be able to send.. I would like to send a little token. ;) This is working perfect... I do have one more thing I forget to mention the text Avails & Left could it moved over into column F. Is this a simple add to the code? You're the best. Gwen |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding 4 rows after each total criteria in the column A with formatting and added text using macro.
Hi Gwen
Glad it is up and running for you... to move the text to column f just offset the activecell by 5 columns like below. ActiveCell.Offset(0,5).Value = "Avails" ActiveCell.EntireRow.Interior.ColorIndex = 5 ActiveCell.Offset(1, 5).Value = "Left" ActiveCell.Offset(1, 0).EntireRow.Interior.ColorIndex = 6 you can find address details on my profile take it easy Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Formatting - Total column headings/rows | Excel Discussion (Misc queries) | |||
run a macro which looks at ranges with added rows | New Users to Excel | |||
HOW DO I RE-NUMBERED MY NUMBER COLUMN AFTER I ADDED ROWS? | Excel Discussion (Misc queries) | |||
Add total number of rows (text) in a column | Excel Worksheet Functions | |||
macro - adding rows to a column that is summed | Excel Discussion (Misc queries) |