radrick60
asked on
Create an excel macro
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
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
ASKER
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
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
Some more
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
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
ASKER
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
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
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
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
ASKER
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
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
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
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
ASKER
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.
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.
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
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
ASKER
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
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
ok fine then here is your revised sub (I did not create anything new just replace your old Sub AddFormulas find this one.
Nothing has changed in the processing just before your CTL SHIFT C will work now as well.
Let me know
gowflow
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 SubNothing has changed in the processing just before your CTL SHIFT C will work now as well.
Let me know
gowflow
ASKER
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
Before I download it would you please add todays date at the top of the macro.
Thanx Tom
you won't mess just put this
'11/24/2014
gowflow
'11/24/2014
gowflow
ASKER
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
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
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
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
ASKER
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
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
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
There is no old one anymore. Only the new one. SAVE button ??? I am dreaming !! Save the workbook.
gowflow
ASKER
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
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
PLease I am not hired to work for you. You state what is the problem then I will see to it.
gowflow
gowflow
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
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
ASKER
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
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
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
gowflow
ASKER
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
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
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.
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
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 SubWhen 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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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
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
Opened module 1 and copied the code from post 40468842
WRONG !!!
Opened module 1 and copied the code from post ID: 40468963
gowflow
ASKER
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
Thanx so very much for your help. I fully realize its not easy working with a low tech person
Tom
ASKER
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.
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.
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
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
ASKER
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
I don't understand your "objection"
Suggest you create a Check the box" type of survey if you want more from your participants
@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
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
ASKER
I accept the experts solution
Tom
Tom
@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
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
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