Adding 4 rows after each total criteria in the column A with formatting and added text using macro.

[simplymidori76]

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

 

[Incidental]

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

 

[simplymidori76]

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.

 

[simplymidori76]

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

 

[Incidental]

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

 

[Incidental]

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

 

[simplymidori76]

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/Rf_jF7Txk6I/AAAAAAAAANk/zyRycI54QQs/s1600-h/example.bmp

 

[Incidental]

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

 

[simplymidori76]

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

 

[Incidental]

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