Solved

Create an excel macro

Posted on 2014-11-17
38
114 Views
Last Modified: 2014-12-03
My PC has Windows 8 installed and am using the latest version of excel

The attached file is a detailed description of the excel macro I require.

Refer to the COMPUTE DESCRIPTION tab for the requirements
SUPER-MACRO1--Autosaved-.xlsm
0
Comment
Question by:radrick60
  • 18
  • 18
38 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40449216
In 2 words I think I understood your request. You need to 'revise' the existing macro so it computes totals as described in sheet2 under every section of data that is found in several workbooks.

MY QUESTIONS:
1) The data you have is always separated by 4 blank lines at least between each block ? or the blocks are only separated by 1 blank row ?
2) Maybe you mentioned it but I will ask it again the range of columns that we need to plug the totals/Average in under every block is always the same ? like Col G to P and consist of 3 rows ?
3) also you may have answered this but will ask all data in the workbooks that need compute results/totals are in the first sheet of the workbook (whether Sheet1 or other) or could be an other sheet ???

These 2 questions necessary to determine if really the Put the cursor thing under each block necessary. I believe it is not but will wait for your answer.

gowflow
0
 

Author Comment

by:radrick60
ID: 40449493
THANX FOR YOUR QUICK RESPONSE

First of all, I am low tech and might ask dumb questions, so please bare with me

You can either modify the compute macro or create a new one .... your choice

ANS TO QUESTIONS:

1.The data blocks are always separated by at LEAST 4 blank rows. There could be more blank rows.

2.Always the same ..... column G thru P with the 3 rows of defined calculations

3. The required calculations can be on any sheet in the workbook and the number of sheets in the workbooks could vary.
     If there are 6 data blocks on a sheet, the cursor will be moved to Column G of any data block and the macro executed.
     This means the macro would be executed 6 times on this sheet . There is no dependency among the data block
     calculations.

If you run the macro on the sample data of SHEET2 (5 blocks of data), and get the results indicated, it's pretty darn sure the macro is performing accurately

Also, please add your name or initials and the date at the beginning of the macro code. This would be very helpful in the event we need to modify the macro to get the desired results.

If your not sure about any of the requirements, let me know and I will clarify

                                                                    Tom
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40449622
Some more


3. The required calculations can be on any sheet in the workbook and the number of sheets in the workbooks could vary.
      If there are 6 data blocks on a sheet, the cursor will be moved to Column G of any data block and the macro executed.
      This means the macro would be executed 6 times on this sheet . There is no dependency among the data block
      calculations.

1) Are all sheets in the workbooks being processed can accommodate this type of totals ?
OR
You may have sheets in these workbooks that need NO Totals to be processed ?

I need to know your requirement and NOT what the present macro do !!!

2) No need for signature. You may post any time a new question and nice this is that in EE you can send a message to get help to the Expert so up to you to follow-up.

Let me know
gowflow
0
 

Author Comment

by:radrick60
ID: 40449887
ANS TO QUESTIONS:

 1. Most sheets in the workbook do NOT need this calculation. Again, the number of sheets in any workbook will vary

 2. As far as my requirements, the COMPUTE DESCRIPTION tab on the excel file outlines the requirements in detail.
     Advise if you need further clarification

                                                          Tom
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40457019
Sorry For late reply due to a flew.

To sum up I understand following pls confirm:

1) You want the macro to act on the 'select sheet' (this means if you believe a certain sheet in a workbook need to have totals then you select that sheet and activate the macro.
2) The macro when is run will do 1 single pass on the whole worksheet from the first row to the last row and will incorporate the totals where it needs to under each block as you mentioned clearly in the attached files.
3) We are talking about a new macro being designed and not the existing one.

Are you ok with that ?

Last minute review:
I just reviewed the code in oyur workbook and noticed that you have 3 modules (module1,2,3) that all 3 have the same Sub
Sub AddFormulas(strtRange As Range)
This is very unhealthy. Please advise which one is the correct one for you (If you know) as we need to disable the other 2 by simply renaming them
gowflow
0
 

Author Comment

by:radrick60
ID: 40458867
To illustrate how the macro will be run for updating data, please refer to sheet2

First, select sheet2 .... because it needs to be updated. The other sheets in the workbook may also require a similar update after completing the sheet 2 update.

Referencing sheet2, my procedure would be:

Place the cursor on G15 then run the macro
then place the cursor on G27 then run the macro
then place the cursor on G41 then run the macro
then place the cursor on G55 then run the macro
then place the cursor on G66 then run the macro
The correct results of each macro execution is show on sheet2. If you get these results the macro is performing properly

Do NOT want the macro to do the entire sheet in one calculation .... as illustrated above the macro was executed 5 times to update the entire sheet

A new macro would be fine .... your call.
The current macro prompts for a starting location for the macro ...... would like to maintain this prompt feature.
To illustrate this macro operation, place the cursor on G15 in sheet2, then press CTRL+SHIFT+C. The macro prompts for a cell location. In this case A1 is entered (starting point for the data block calculations) and ENTER is pressed. The results are show .... with format and locations not correct.
I am currently using this macro, but as you can see I must do some clean up to get the desired display/calculations.

The macro code in the COMPUTE sheet is assigned the CTRL+SHIFT+C keys to execute ...... this is what I currently use for my calculations  and would not want any changes until your code is performing properly.

As to the "other" code changes I see no problem in your changing the macro name.  If you do change any names, please ... please.... add a comment at the beginning of the code stating " Name was change from xxx to yyy "and enter the date changed. I am low tech and this will help me sort out the macros I need after I get your new compute code

Thanx                                                         Tom
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40459557
I do not understand why:

Do NOT want the macro to do the entire sheet in one calculation .... as illustrated above the macro was executed 5 times to update the entire sheet

??
My macro will not prompt for a cell or anything it will process the whole sheet. What is the problem with that ? If at the end you will get the same result i.e.

1) Get the totals under each section where they should be
2) Get all the totals for all the sections.

PLease clarify as this is important.
gowflow
0
 

Author Comment

by:radrick60
ID: 40459604
The macro MUST perform  on one data block at a time as I described ..... doing the entire sheet is not desireable.

It's an operational reason that I don't want to get into .... would have to generate another sample workbook to explain and illustrate the reasons. The one data block calculation simply gives me more flexability in the application.
Trust me on this one.

The lack of a macro prompt is OK ..... the prompt gave me a warm fuzzy feeling the calculation was starting at the correct point, and the resulting calculations were correct.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40459614
oops I think you did not get my point.

The macro will do first block
put the totals
..
then
move to next block below it
put the totals
..
then
move to next block below it
....
Until end of data
then will stop

is this also a problem ?
gowflow
0
 

Author Comment

by:radrick60
ID: 40459825
I believe I understand your point .... one macro execution does the calculations for all data blocks on the sheet

NOT wanted.

Want the macro to do the first block calculation and put the totals . THEN STOP!

I will initiate the execution of the macro on the next block.   per my description in ID40457019 11-21-14

Need this type of operation to get the most out of the macro

Again, simply do NOT want the macro to calculate data blocks of the entire worksheet with one macro run.

Got to do one data block calculation at a time .... requiring the macro run for each data block on the sheet.

Did you run the CTRL-SHIFT-C macro on sheet2?

                                                Tom
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40460522
ok fine then here is your revised sub (I did not create anything new just replace your old Sub AddFormulas find this one.

Sub AddFormulas(strtRange As Range)
                
    'find the end of the data
    Dim rngStart As Range, rngEnd As Range
    Set rngStart = strtRange.Offset(0, strtRange.Column * -1 + 1)
    Set rngEnd = rngStart.End(xlDown)
    rowStart = Format(rngStart.Row, "#0")
    rowend = Format(rngEnd.Row, "#0")
                
    'original code by Rgonzo1971
    Range("G" & rowend + 1).Formula = "=COUNT(G" & rowStart & ":G" & rowend & ")"
    Range("I" & rowend + 1).Formula = "=AVERAGE(I" & rowStart & ":I" & rowend & ")"
    Range("J" & rowend + 1).Formula = "=AVERAGE(J" & rowStart & ":J" & rowend & ")"
    Range("L" & rowend + 1).Formula = "=AVERAGE(L" & rowStart & ":L" & rowend & ")"
    Range("N" & rowend + 1).Formula = "=AVERAGE(N" & rowStart & ":N" & rowend & ")"
    Range("P" & rowend + 1).Formula = "=AVERAGE(P" & rowStart & ":P" & rowend & ")"
    Range("J" & rowend + 3).Formula = "=COUNTIF(J" & rowStart & ":J" & rowend & "," & Chr(34) & "<0" & Chr(34) & ")"
    Range("L" & rowend + 3).Formula = "=COUNTIF(L" & rowStart & ":L" & rowend & "," & Chr(34) & "<0" & Chr(34) & ")"
    Range("N" & rowend + 3).Formula = "=COUNTIF(N" & rowStart & ":N" & rowend & "," & Chr(34) & "<0" & Chr(34) & ")"
    
    '---> Disabled formulaR1C1
    'Range("K" & rowEnd + 2).FormulaR1C1 = "=(R[-1]C[-4]-R[1]C[-1])/R[-1]C[-4]"
    'Range("O" & rowEnd + 2).FormulaR1C1 = "=(R[-1]C[-8]-R[1]C[-1])/R[-1]C[-8]"
                    
    '---> Replaced R1C1 and added missing
    Range("N" & rowend + 2).Formula = "=(G" & rowend + 1 & "-N" & rowend + 3 & ")/G" & rowend + 1
    Range("L" & rowend + 2).Formula = "=(G" & rowend + 1 & "-L" & rowend + 3 & ")/G" & rowend + 1
    Range("J" & rowend + 2).Formula = "=(G" & rowend + 1 & "-J" & rowend + 3 & ")/G" & rowend + 1
    Range("P" & rowend + 3) = "C"
    
    '---> Format Data
    Range(rowend + 1 & ":" & rowend + 3).HorizontalAlignment = xlCenter
    Range(rowend + 1 & ":" & rowend + 3).Font.Bold = True
    Range("I" & rowend + 1).NumberFormat = "0"
    Range("J" & rowend + 1 & ":P" & rowend + 1).NumberFormat = "0.0%;[Red] -0.0%"
    Range("J" & rowend + 2 & ":N" & rowend + 2).NumberFormat = "0%"
    Range("J" & rowend + 3 & ":N" & rowend + 3).NumberFormat = "[Red]0"
End Sub

Open in new window


Nothing has changed in the processing just before your CTL SHIFT C will work now as well.
Let me know

gowflow
0
 

Author Comment

by:radrick60
ID: 40460718
I don't want to mess with editing the macro.

Before I download it would you please add todays date at the top of the macro.

Thanx      Tom
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40461544
you won't mess just put this

'11/24/2014

gowflow
0
 

Author Comment

by:radrick60
ID: 40464010
am now displaying my low tech capability ....

Tryng to load and test your  modified code. These are the step I have taken:

Copied your code in a sheet of the SUPER-MACRO file

Pressed ALT + F11 ..... displayed the modules

Copied your code into a module (module 4)

"X ed" out of Module 4

Macro review didn't list your macro ????

Am I loading the macro wrong ..... and if so, is there an easy way to load the macro?

Tried executing the CTR+SHIFT+C macro and it runs the same as always .... same shortcomings

                                                      Tom
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40464382
OMG !!! You need Macro basics 101 !!! :)

As I mentioned before you had too many same macros on your file so you need to clean them up. So I will give you all the steps.

1) Open your workbook that contains the macros. Press ALT F11
2) On the left pane you will see 5 modules from which 3 are useless as they are either empty or simply a copy of Module1 that will need to delete. select Module2 right click and choose Delete, Select Module3 right click and select delete and select module4 right click and select delete.
3) Now we are endup with Module1 and Module5.
4) Doubleclcik on Module 1 and it will display the code in the right pane.
5) You will notice in the left bottom of the window of the right pane a small icon that can be toggled tick on it and it will display 1 Sub at a time.
6) From the top right dropdown, Select the Sub AddFormulas and once it is displayed select all the code from the first line Sub AddFormulas .... till the last line End Sub and delete the code that is there.
7) Now you will end up only with 1 sub which is AddSummary which is fine.
8) At this stage we need to get here your new Sub that I posted in this here. to do this as follows:
9) Locate the code in here and press Select All under its window then point your cursor over the code right click select Copy
10) Go back to your module 1 and click anywhere after the End Sub and choose Paste and you should get the new code there.
11) MAKE SURE TO PRESS SAVE at this point.
12) Now all you need to do is close the workbook, open it select Sheet2 and press CTRL SHIFT C and do what you need to do.

Let me know.
gowflow
0
 

Author Comment

by:radrick60
ID: 40464459
I do appreciate you helping this low tech guy out.
One thing.

The current CTR+SHIFT+C macro although needing manual intervention after running it, allows me to do my computing. Without it the job is too complex and time consuming and I cant do my work.

Would appreciate it you could tell me how to preserve this macro as is:

After using your updated version for awhile I can then delete the old one.

11) MAKE SURE TO PRESS SAVE at this point ....... were is the SAVE button??????

Thanx                                Tom
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40464924
Preserve this one and delete the old one ?????

There is no old one anymore. Only the new one. SAVE button ??? I am dreaming !! Save the workbook.
gowflow
0
 

Author Comment

by:radrick60
ID: 40466322
A little scary for me .... the "old" CTRL+SHIFT+C macro is gone.

Ran your modified code using (CRTL+SHIFT+C) on SHEET 2 of the attached file.

Placed the cursor on G41, G55, and G66 respectively on sheet 2 and ran the macro.
Comparing the results of the three runs with the desired display directly below the macro results illustrates the areas that require additional work.

Before you send me new code please run the macro on sheet2 at the above locations.

Also, please date the new code at the top

Thanx                                       Tom
SUPER-MACRO1--Autosaved-.xlsm
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 29

Expert Comment

by:gowflow
ID: 40466938
PLease I am not hired to work for you. You state what is the problem then I will see to it.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40466951
Well just saw what you tried to attempt. We don't care where you place your cursor it does not matter !!!! all you need to do is answer the question on the inputbox that comes up.

The inputbox says: Please enter the first cell of a data block.
For your first block the first cell is A1 so you enter A1 and check the results.
For your second block the first cell is A23 so you enter A23 and check the results.
For your third block the first cell is A35 so you enter A35 and check the results.
etc...

Let me know.
gowflow
0
 

Author Comment

by:radrick60
ID: 40467934
Didn't want to give the impression that you were hired to work for me.  ....... I certainly didn't want to give that impression. Trying to describe my problems the best I can.

It seems logical to run the macro on the cells in sheet 2 as indicated above . Comparing the obtain results with the desired results (below the obtained results) is a clear indication of the difference.  That is the problem.

Would you rather I do something different to identify my problem with the macro?

Please advise                                                Tom
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40468451
I ran it and it give exactly the same result. pls be specific what do you input as first cell in the block ??? Did you read my post ID: 40466951 ??

gowflow
0
 

Author Comment

by:radrick60
ID: 40468717
I did read your post and followed your instructions.

This is what I did and the results on running the macro on sheet2 for the third data block

* Placed cursor on G41 .... your right ... the cursor can be located anywhere
* Pressed CTR+SHIFT+C
* Message appeared: Select the first cell on a data block
*Selected the third block and Entered A35 then OK

The displayed results on sheet2 are as follows:

                  G               H              I              J              K               L               M               N               O               P

41             6                           45.667      6.8%                        2.8%                           -1.3%                         -36.3%

42                                                                          0.667                                                               0.5
 
43                                                              2                                                                   3

           All test was normal black, left justified


                                                                                        DESIRED RESULTS

                G               H              I              J              K               L               M               N               O               P

41             6                             46          6.8%                        2.8%                           -1.3%                         -36%

42                                                           67%                         67%                              50%
 
43                                                              2                              2                                   3                                C

                 All cell entries are Bold Black, except for locations P41, J43, L43, and N43 which is Bold RED.
                 All cell entries are centered, have a 16 point arial font, and are decimal format per the display above.

Do you get the desired results when you run the macro?

                                                                Tom
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40468842
Don't really know what you did but your still running the OLD macro !!!

Did you remove the Module2, Module3, Module4 as if you did not and put the new macro in Module4 it will still run the old macro.

You need to have ONLY
Module1 that has 2 subs
Sub AddSummary()
Sub AddFormulas(strtRange As Range)

and
Module5
Sub addspace()

Sub AddFormulas is the one I posted earlier and will repost again herebelow.

Sub AddFormulas(strtRange As Range)
                
    'find the end of the data
    Dim rngStart As Range, rngEnd As Range
    Set rngStart = strtRange.Offset(0, strtRange.Column * -1 + 1)
    Set rngEnd = rngStart.End(xlDown)
    rowStart = Format(rngStart.Row, "#0")
    rowend = Format(rngEnd.Row, "#0")
                
    'original code by Rgonzo1971
    Range("G" & rowend + 1).Formula = "=COUNT(G" & rowStart & ":G" & rowend & ")"
    Range("I" & rowend + 1).Formula = "=AVERAGE(I" & rowStart & ":I" & rowend & ")"
    Range("J" & rowend + 1).Formula = "=AVERAGE(J" & rowStart & ":J" & rowend & ")"
    Range("L" & rowend + 1).Formula = "=AVERAGE(L" & rowStart & ":L" & rowend & ")"
    Range("N" & rowend + 1).Formula = "=AVERAGE(N" & rowStart & ":N" & rowend & ")"
    Range("P" & rowend + 1).Formula = "=AVERAGE(P" & rowStart & ":P" & rowend & ")"
    Range("J" & rowend + 3).Formula = "=COUNTIF(J" & rowStart & ":J" & rowend & "," & Chr(34) & "<0" & Chr(34) & ")"
    Range("L" & rowend + 3).Formula = "=COUNTIF(L" & rowStart & ":L" & rowend & "," & Chr(34) & "<0" & Chr(34) & ")"
    Range("N" & rowend + 3).Formula = "=COUNTIF(N" & rowStart & ":N" & rowend & "," & Chr(34) & "<0" & Chr(34) & ")"
    
    '---> Disabled formulaR1C1
    'Range("K" & rowEnd + 2).FormulaR1C1 = "=(R[-1]C[-4]-R[1]C[-1])/R[-1]C[-4]"
    'Range("O" & rowEnd + 2).FormulaR1C1 = "=(R[-1]C[-8]-R[1]C[-1])/R[-1]C[-8]"
                    
    '---> Replaced R1C1 and added missing
    Range("N" & rowend + 2).Formula = "=(G" & rowend + 1 & "-N" & rowend + 3 & ")/G" & rowend + 1
    Range("L" & rowend + 2).Formula = "=(G" & rowend + 1 & "-L" & rowend + 3 & ")/G" & rowend + 1
    Range("J" & rowend + 2).Formula = "=(G" & rowend + 1 & "-J" & rowend + 3 & ")/G" & rowend + 1
    Range("P" & rowend + 3) = "C"
    
    '---> Format Data
    Range(rowend + 1 & ":" & rowend + 3).HorizontalAlignment = xlCenter
    Range(rowend + 1 & ":" & rowend + 3).Font.Bold = True
    Range("I" & rowend + 1).NumberFormat = "0"
    Range("J" & rowend + 1 & ":P" & rowend + 1).NumberFormat = "0.0%;[Red] -0.0%"
    Range("J" & rowend + 2 & ":N" & rowend + 2).NumberFormat = "0%"
    Range("J" & rowend + 3 & ":N" & rowend + 3).NumberFormat = "[Red]0"
End Sub

Open in new window


When you click on Module1 in the dropdown top right you should only have the above 2 subs only if you have 2 times AddFormulas this means you have the old and the new and as they are the same name the system is calling the old before the new so you need to delete the old one like I already explained earler.

gowflow
0
 

Author Comment

by:radrick60
ID: 40468940
Working with SUPER MACRO file

I think we are getting closer .... but the disturbing thing right now is when I press ALT+F11, a left pane does not appear showing the modules

I click on Windows and Module3,6,7 and sheet 6 is displayed.

Module3 has your latest code entered which will probably work if we can get it to run. modules 6 and 7 are blank
Sheet6 display is a mystery .... there is no sheet 6 in my SUPER MACRO file

Pressing the view macros shows module5( Add Space) which is a macro that runs great for me.

This implies there are modules 1 thru 4 are also present but I cant see them???

My panic now is that CTR+SHIFT+C current does not run at all.

Would it help if I sent you the SUPER MACRO file?

                                                                 Tom
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40468963
Go back to the basics and follow instructions on ID: 40464382

or
Just create a blank new Excel same it to the name you want and type Macro Enabled it should be .xlsm
then press ALT F11

in the left pane you will see the name of your file with the sheets that are there.
right click on the name of the file and choose Insert Module
it will create Module1
doubleclick on Module1 and insert the below code

Sub addspace()
    Application.ScreenUpdating = False
    
        Dim lrow As Long, srow As Long
        Dim x As Long
        srow = 6    '<--- Your Starting Point
    
        Do Until srow > Cells(Cells.Rows.Count, "B").End(xlUp).Row
    
            If Cells(srow - 1, "b").Value = Cells(srow, "b").Value Then
                srow = srow + 1
            Else
               For x = 0 To 3
                Rows(srow + x).Insert
             Next x
                srow = srow + 5
            End If
        Loop
    
    Application.ScreenUpdating = True
    End Sub

Sub AddSummary()
                
    Dim mySel As Range
    Set mySel = Application.InputBox(prompt:="Select the first cell of a data block", Title:="Select start", Type:=8)
    AddFormulas mySel
                    
End Sub
                
Sub AddFormulas(strtRange As Range)
                
    'find the end of the data
    Dim rngStart As Range, rngEnd As Range
    Set rngStart = strtRange.Offset(0, strtRange.Column * -1 + 1)
    Set rngEnd = rngStart.End(xlDown)
    rowStart = Format(rngStart.Row, "#0")
    rowend = Format(rngEnd.Row, "#0")
                
    'original code by Rgonzo1971
    Range("G" & rowend + 1).Formula = "=COUNT(G" & rowStart & ":G" & rowend & ")"
    Range("I" & rowend + 1).Formula = "=AVERAGE(I" & rowStart & ":I" & rowend & ")"
    Range("J" & rowend + 1).Formula = "=AVERAGE(J" & rowStart & ":J" & rowend & ")"
    Range("L" & rowend + 1).Formula = "=AVERAGE(L" & rowStart & ":L" & rowend & ")"
    Range("N" & rowend + 1).Formula = "=AVERAGE(N" & rowStart & ":N" & rowend & ")"
    Range("P" & rowend + 1).Formula = "=AVERAGE(P" & rowStart & ":P" & rowend & ")"
    Range("J" & rowend + 3).Formula = "=COUNTIF(J" & rowStart & ":J" & rowend & "," & Chr(34) & "<0" & Chr(34) & ")"
    Range("L" & rowend + 3).Formula = "=COUNTIF(L" & rowStart & ":L" & rowend & "," & Chr(34) & "<0" & Chr(34) & ")"
    Range("N" & rowend + 3).Formula = "=COUNTIF(N" & rowStart & ":N" & rowend & "," & Chr(34) & "<0" & Chr(34) & ")"
    
    '---> Disabled formulaR1C1
    'Range("K" & rowEnd + 2).FormulaR1C1 = "=(R[-1]C[-4]-R[1]C[-1])/R[-1]C[-4]"
    'Range("O" & rowEnd + 2).FormulaR1C1 = "=(R[-1]C[-8]-R[1]C[-1])/R[-1]C[-8]"
                    
    '---> Replaced R1C1 and added missing
    Range("N" & rowend + 2).Formula = "=(G" & rowend + 1 & "-N" & rowend + 3 & ")/G" & rowend + 1
    Range("L" & rowend + 2).Formula = "=(G" & rowend + 1 & "-L" & rowend + 3 & ")/G" & rowend + 1
    Range("J" & rowend + 2).Formula = "=(G" & rowend + 1 & "-J" & rowend + 3 & ")/G" & rowend + 1
    Range("P" & rowend + 3) = "C"
    
    '---> Format Data
    Range(rowend + 1 & ":" & rowend + 3).HorizontalAlignment = xlCenter
    Range(rowend + 1 & ":" & rowend + 3).Font.Bold = True
    Range("I" & rowend + 1).NumberFormat = "0"
    Range("J" & rowend + 1 & ":P" & rowend + 1).NumberFormat = "0.0%;[Red] -0.0%"
    Range("J" & rowend + 2 & ":N" & rowend + 2).NumberFormat = "0%"
    Range("J" & rowend + 3 & ":N" & rowend + 3).NumberFormat = "[Red]0"
End Sub

Open in new window


Save your workbook
All you need to do is to get your sheet2 in this workbook.
Once done display the macros and click on AddSummary and press on Options and in the textbox put C beside CTRL+Shift press ok and try it.

gowflow
gowflow
0
 

Author Comment

by:radrick60
ID: 40469044
Opened a new file like you suggested.

The problem is that when  I press ALT F11 .... I don't get the left pane to appear.

It shows module3 of the super macro file???

                              Tom
0
 

Author Comment

by:radrick60
ID: 40469157
New Development

I closed out excel and started over.

Opened the MACRO ENABLER file as you suggested.

Now when I press ALT F11 .... I get the left panel !!

Opened module 1 and copied the code from post 40468842

The ADD SPACE macro is working fine and don't want to mess with it

Post 40468842 is for the formulas only ... correct?

To try and get the formula macro I closed module 1("x" ) .... then saved the file.

Looked at macro view and the add formula macro was NOT there???

Getin closer                                   Tom



Any thoughts?

                                                         Tom
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40469163

Opened module 1 and copied the code from post 40468842

WRONG !!!
Opened module 1 and copied the code from post ID: 40468963

gowflow
0
 

Author Comment

by:radrick60
ID: 40469424
YIPPI !!!   YIPPI!!!   YIPPI!!!    ........ IT WORKS LIKE A CHAMP .... AM BACK IN BUSINESS

Thanx so very much for your help. I fully realize its not easy working with a low tech person


                                                        Tom
0
 

Author Comment

by:radrick60
ID: 40469523
I've requested that this question be closed as follows:

Accepted answer: 0 points for radrick60's comment #a40469424

for the following reason:

I am a low tech person and that made the job of helping me more difficult.

I am sure the expert said to himself .... this guy doesn't know much about computers and he was correct.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40469459
Yippi Ypii means you found the solution ???

If yes well YOu closed the question again wrong you gave yourself 0 points so this question will be closed with no points to the Expert who gave you the solution is that what you want ???

If no then RELAX
all you need to so is simply select the comment that was the go for you that the expert posted and allow him the points that you feel he/she deserves this way the expert gets the points and the question get closed properly.

gowlfow
0
 

Author Comment

by:radrick60
ID: 40469550
I accepted the solution and commented on the results.

I don't understand your "objection"

Suggest you create a Check the box" type of survey if you want more from your participants
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40470116
@eenookami

Thank you for your help in resolving issue. As per all the Threads above one can note that OP not at all focus on detail as per numerous instructions and clear on how to implement solution still this was not follows so it is normal to assume that proper closing of this question is not intentional but rather lacks preciseness.

I suggest you point out to OP clearly that if he/she accept Expert's solution for that matter writer that he/she need to simply 'tick' the comment of the said expert that he/she feels is the solution for him/her without having to also tick his own comment and attribute the desired points.

I think maybe this should help resolve matter positively.

Regards
gowflow
0
 

Author Comment

by:radrick60
ID: 40470324
I accept the experts solution

Tom
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40470806
@radrick60

So simply close this question and make sure you only tick on the Comment that you feel is the solution for you and allow the points as you feel appropriate.

This question has been re-opened.

gowflow
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Sometimes a user will call me frantically, explaining that something has gone wrong and they have tried everything (read - they have messed it up more and now need someone to clean up) and it still does no good, can I help them?!  Usually the standa…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
The viewer will learn how to successfully download and install the SARDU utility on Windows 8, without downloading adware.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now