radrick60
asked on
CREATE AN EXCEL MACRO
I have been trying to create an Excel macro using the MACRO RECORDER with no success.
Finally came to the conclusion that the macro I need cannot be accomplished using the macro recorder feature in Excel
I am not at all versed with VBA coding of excel macros and request you generate a macro that would save me significant time when updating my data.
The following are the "specifications" of the macro
* Sheet1 of the attached file (SUPER MACRO) is the starting configuration of the spreadsheet
* Sheet2 is the results after the macro is executed
OVERVIEW OF MACRO REQUIREMENTS:
* The macro will be executed on about 50 different spreadsheets when the data
requires an update.
* Th column locations for the macro execution will be the SAME.
* The row locations for the macro execution will NOT be the same
MACRO CELL FORMAT:
* All cell locations accepting macro related calculations will use the Arial font, be 16
point, and will be centered in the cell. Reference Sheet2
* The format (decimal/%) of cell locations accepting macro related calculations shall be
formatted per sheet2
* Note the locations where the number in red are required. see Sheet2
MACRO PROCEDURE: ...... refer to sheet2 for the referenced cell positions
1. Place the cursor at G15 .... starting point for the macro
2. Calculate the COUNT MUMBERS from G4 to G14 range
3. Move the cursor to I15
4. Calculate the AVERAGE from I4 to I14 range
5. Move the cursor to J15
6. Calculate the AVERAGE from J4 to J14 range
7. Move the cursor to L15
8. Calculate the AVERAGE from L4 to L14 range
9. Move the cursor to N15
10. Calculate the AVERAGE from N4 to N14 range
11. Move the cursor to P15
12. Calculate the AVERAGE from P4 to P14 range
13. Move the cursor to J17
14. Calculate the number of NEGATIVE numbers in the J4 to J14 range
i.e. =COUNTIF(J4:J14,"<"&0)
15. Move the cursor to K16
16. Calculate the win percentage in K16
i.e. =(G15-J17)/G15
17. Move the cursor to N17
18. Calculate the number of NEGATIVE numbers in the N4 to N14 range
i.e. =COUNTIF(N4:N14,"<"&0
18. Move the cursor to O16
19. Calculate the win percentage in O16
i.e. =(G15-N17)/G15
20. Move cursor to G15
The entries in J16 and N16 will be done manually
--------------------------
To complete the calculations on the remainder of the sheet
Manually move the cursor to location G45
Run the macro ....... results per sheet2
Manually move the cursor to location G53
Run the macro ..... results per sheet2
Manually move the cursor to G73
Run the macro .... results per sheet2
Manually move the cursor to location G86
Run the macro .... results per sheet2
MACRO VERIFICATION:
* The results of running the macro on sheet1 should comply with the results on sheet2
* The results of running the macro on sheet3 should comply with the results on sheet4
Finally came to the conclusion that the macro I need cannot be accomplished using the macro recorder feature in Excel
I am not at all versed with VBA coding of excel macros and request you generate a macro that would save me significant time when updating my data.
The following are the "specifications" of the macro
* Sheet1 of the attached file (SUPER MACRO) is the starting configuration of the spreadsheet
* Sheet2 is the results after the macro is executed
OVERVIEW OF MACRO REQUIREMENTS:
* The macro will be executed on about 50 different spreadsheets when the data
requires an update.
* Th column locations for the macro execution will be the SAME.
* The row locations for the macro execution will NOT be the same
MACRO CELL FORMAT:
* All cell locations accepting macro related calculations will use the Arial font, be 16
point, and will be centered in the cell. Reference Sheet2
* The format (decimal/%) of cell locations accepting macro related calculations shall be
formatted per sheet2
* Note the locations where the number in red are required. see Sheet2
MACRO PROCEDURE: ...... refer to sheet2 for the referenced cell positions
1. Place the cursor at G15 .... starting point for the macro
2. Calculate the COUNT MUMBERS from G4 to G14 range
3. Move the cursor to I15
4. Calculate the AVERAGE from I4 to I14 range
5. Move the cursor to J15
6. Calculate the AVERAGE from J4 to J14 range
7. Move the cursor to L15
8. Calculate the AVERAGE from L4 to L14 range
9. Move the cursor to N15
10. Calculate the AVERAGE from N4 to N14 range
11. Move the cursor to P15
12. Calculate the AVERAGE from P4 to P14 range
13. Move the cursor to J17
14. Calculate the number of NEGATIVE numbers in the J4 to J14 range
i.e. =COUNTIF(J4:J14,"<"&0)
15. Move the cursor to K16
16. Calculate the win percentage in K16
i.e. =(G15-J17)/G15
17. Move the cursor to N17
18. Calculate the number of NEGATIVE numbers in the N4 to N14 range
i.e. =COUNTIF(N4:N14,"<"&0
18. Move the cursor to O16
19. Calculate the win percentage in O16
i.e. =(G15-N17)/G15
20. Move cursor to G15
The entries in J16 and N16 will be done manually
--------------------------
To complete the calculations on the remainder of the sheet
Manually move the cursor to location G45
Run the macro ....... results per sheet2
Manually move the cursor to location G53
Run the macro ..... results per sheet2
Manually move the cursor to G73
Run the macro .... results per sheet2
Manually move the cursor to location G86
Run the macro .... results per sheet2
MACRO VERIFICATION:
* The results of running the macro on sheet1 should comply with the results on sheet2
* The results of running the macro on sheet3 should comply with the results on sheet4
ASKER
Identifying rows:
reference sheet2, the macro must be run after the cursor is manually placed on G15
The macro is run and executes the functions described
The cursor is then manually moved to G45 and the macro is run again
The cursor is them manually moved to G53 and the macro is run again
The cursor is them manually moved to G73 and the macro is run again
The cursor is them manually moved to G86 and the macro is run again
Looking at sheet4 ..... the row starting point of each macro is different than that of sheet2
The columns are the same for both sheets.
Hope this explains the row selections .... the row starting point of the macro is determined manually
I have 4 different files each containing several sheets that will use this macro
Thanx Tom
reference sheet2, the macro must be run after the cursor is manually placed on G15
The macro is run and executes the functions described
The cursor is then manually moved to G45 and the macro is run again
The cursor is them manually moved to G53 and the macro is run again
The cursor is them manually moved to G73 and the macro is run again
The cursor is them manually moved to G86 and the macro is run again
Looking at sheet4 ..... the row starting point of each macro is different than that of sheet2
The columns are the same for both sheets.
Hope this explains the row selections .... the row starting point of the macro is determined manually
I have 4 different files each containing several sheets that will use this macro
Thanx Tom
Hi Tom,
So the query regarding different number of rows relates to the different blocks of data.
Will the number of rows per block stay the same on each update? I assume not, hence the need for adjusting the formulas on each update. If the size of each block is staying the same, what is overwriting the formulas on each update?
To summarise the requirements of the macro:
1) Go to first block of data, add formulas below for counts and average,
2) Find next block of data and repeat addition of formulas,
3) Find next block and repeat
4) Repeat until last block dealt with.
Will there be a fixed number of blocks of data per sheet?
Thanks
Rob
So the query regarding different number of rows relates to the different blocks of data.
Will the number of rows per block stay the same on each update? I assume not, hence the need for adjusting the formulas on each update. If the size of each block is staying the same, what is overwriting the formulas on each update?
To summarise the requirements of the macro:
1) Go to first block of data, add formulas below for counts and average,
2) Find next block of data and repeat addition of formulas,
3) Find next block and repeat
4) Repeat until last block dealt with.
Will there be a fixed number of blocks of data per sheet?
Thanks
Rob
ASKER
Rob:
Yes. Each block of data (rows) will be different (usually), on occasion they may be the same.
Using the word "find" may be misleading. The cursor is manually moved to the next block ... it is not part of the macro. Upon placing the cursor on the correct cell, the macro is executed.
The number of blocks of data on a sheet will be different .... look at Sheet2 and Sheet4
If there is 5 blocks of data on a sheet, the macro will be run 5 times.
The formulas added are for calculating negative numbers and %wins are in addition to count and average.
Not sure what you mean by "overwiting the formulas" comment.
The starting point for running the macro is shown on Sheets1 and 3. Note the 4 blank rows after the DATA BLOCK. The cells to be updated are blank before the macro is executed.
Thanx Tom
Yes. Each block of data (rows) will be different (usually), on occasion they may be the same.
Using the word "find" may be misleading. The cursor is manually moved to the next block ... it is not part of the macro. Upon placing the cursor on the correct cell, the macro is executed.
The number of blocks of data on a sheet will be different .... look at Sheet2 and Sheet4
If there is 5 blocks of data on a sheet, the macro will be run 5 times.
The formulas added are for calculating negative numbers and %wins are in addition to count and average.
Not sure what you mean by "overwiting the formulas" comment.
The starting point for running the macro is shown on Sheets1 and 3. Note the 4 blank rows after the DATA BLOCK. The cells to be updated are blank before the macro is executed.
Thanx Tom
Hi,
pls try
pls try
Sub Macro3()
For Idx = 1 To 3 Step 2
Set OrigSht = Sheets("Sheet" & Idx)
Set DestSht = Sheets("Sheet" & Idx + 1)
DestSht.Cells.Delete
OrigSht.Cells.Copy Destination:=DestSht.Range("A1")
DestSht.Activate
Range("A1") = "Sheet " & Idx + 1
LastRow = Range("A" & Cells.Rows.Count).End(xlUp).Row
myRow = LastRow
While myRow <> 1
Range("G" & myRow + 1).Formula = "=COUNT(G" & Range("A" & myRow).End(xlUp).Row & ":G" & myRow & ")"
Range("I" & myRow + 1).Formula = "=AVERAGE(I" & Range("A" & myRow).End(xlUp).Row & ":I" & myRow & ")"
Range("J" & myRow + 1).Formula = "=AVERAGE(J" & Range("A" & myRow).End(xlUp).Row & ":J" & myRow & ")"
Range("L" & myRow + 1).Formula = "=AVERAGE(L" & Range("A" & myRow).End(xlUp).Row & ":L" & myRow & ")"
Range("N" & myRow + 1).Formula = "=AVERAGE(N" & Range("A" & myRow).End(xlUp).Row & ":N" & myRow & ")"
Range("P" & myRow + 1).Formula = "=AVERAGE(P" & Range("A" & myRow).End(xlUp).Row & ":P" & myRow & ")"
Range("J" & myRow + 3).Formula = "=COUNTIF(J" & Range("A" & myRow).End(xlUp).Row & ":J" & myRow & "," & Chr(34) & "<0" & Chr(34) & ")"
Range("N" & myRow + 3).Formula = "=COUNTIF(N" & Range("A" & myRow).End(xlUp).Row & ":N" & myRow & "," & Chr(34) & "<0" & Chr(34) & ")"
Range("K" & myRow + 2).FormulaR1C1 = "=(R[-1]C[-4]-R[1]C[-1])/R[-1]C[-4]"
Range("O" & myRow + 2).FormulaR1C1 = "=(R[-1]C[-8]-R[1]C[-1])/R[-1]C[-8]"
myRow = Range("A" & myRow).End(xlUp).End(xlUp).Row
Wend
Next
End Sub
Yes, maybe the use of word find was misleading.
Currently the user manually puts the cursor on the first row of the first block of data, I assume you would like the routine to do all blocks of data on one click, ie user select the first block and click a button and it will do them all.
So by "Find" I meant the routine to determine where each block of data starts and deal with each block in turn.
Rgonzo1971 - doesn't your routine add formulas at the bottom of each sheet rather than after each block of data?
Currently the user manually puts the cursor on the first row of the first block of data, I assume you would like the routine to do all blocks of data on one click, ie user select the first block and click a button and it will do them all.
So by "Find" I meant the routine to determine where each block of data starts and deal with each block in turn.
Rgonzo1971 - doesn't your routine add formulas at the bottom of each sheet rather than after each block of data?
HI Rob
No my macro read Sheet 1 and then 3
Delete the content of sHEET 2 AND 4
and the formulas at the bottom of each block (While ...Wend)
Regards
No my macro read Sheet 1 and then 3
Delete the content of sHEET 2 AND 4
and the formulas at the bottom of each block (While ...Wend)
Regards
ASKER
Rgonzo1971
I assume you are replacing Rob for this task ... is that correct?
I tried the macro above by placing the cursor at G15 of sheet1
Executing the macro jumped me to sheet4???
Dont know if it calculated the required data in sheet4, but it did not calculate the data is sheet1 as it should have.
Before we even try calculating ALL the data block calculations, I would suggest we do one data block at a time to start with. After that works, we can try to do them all with one click
Even doing one block at time would save me a ton of time.
Suggest trying to run the macro after placing the cursor at G15 of sheet1
I assume you are replacing Rob for this task ... is that correct?
I tried the macro above by placing the cursor at G15 of sheet1
Executing the macro jumped me to sheet4???
Dont know if it calculated the required data in sheet4, but it did not calculate the data is sheet1 as it should have.
Before we even try calculating ALL the data block calculations, I would suggest we do one data block at a time to start with. After that works, we can try to do them all with one click
Even doing one block at time would save me a ton of time.
Suggest trying to run the macro after placing the cursor at G15 of sheet1
ASKER
There are no calculations ..... i try to run the macro in sheet1 and it jumps to sheet4 ????
Placing the cursor on G15 in sheet1 and run the macro .... this will clearly demostrate the problem to you.
Are you replacing Rob on this project?
Tom
Placing the cursor on G15 in sheet1 and run the macro .... this will clearly demostrate the problem to you.
Are you replacing Rob on this project?
Tom
Hi Tom,
Not sure where you are getting the impression of RGonzo1971 replacing me!!
We are not in any way associated with each other.
As you are no doubt aware, this is purely a forum for posting suggestions and it is then up to you which suggestion you accept. We are all volunteers and I am chipping in as and when I can with my normal day job workload. Quite busy at the minute so haven't been able to contribute much to this thread but have it on the back burner as and when I do get time.
Thanks
Rob H
Not sure where you are getting the impression of RGonzo1971 replacing me!!
We are not in any way associated with each other.
As you are no doubt aware, this is purely a forum for posting suggestions and it is then up to you which suggestion you accept. We are all volunteers and I am chipping in as and when I can with my normal day job workload. Quite busy at the minute so haven't been able to contribute much to this thread but have it on the back burner as and when I do get time.
Thanks
Rob H
Hi,
Let's try this version
Sheet2 an Sheet4 are empty
then run the macro in it and tell what comes in Sheet2 and 4
Regards
SUPER-MACROv2.xlsm
Let's try this version
Sheet2 an Sheet4 are empty
then run the macro in it and tell what comes in Sheet2 and 4
Regards
SUPER-MACROv2.xlsm
ASKER
Whats this verion ? .... where is the code?
You should try it on sheet1. If it doesnt work for you it certainly wont work for me
If it works, the results of sheet2 should be the same as that in sheet1
Tom
You should try it on sheet1. If it doesnt work for you it certainly wont work for me
If it works, the results of sheet2 should be the same as that in sheet1
Tom
Hi,
The Code is in Module 1
Regards
The Code is in Module 1
Regards
ASKER
Sorry, but I am low tech guy ...... where is module 1?
How do I download it to try it?
Did you try to run the macro on sheet1?
How do I download it to try it?
Did you try to run the macro on sheet1?
Goto View / Macros / View Macros
Choose
'SUPER-MACROv2.xlsm'!Modul
And Run
Choose
'SUPER-MACROv2.xlsm'!Modul
And Run
ASKER
Followered your instructions and couldnt find it
Can you list the code and I will paste it in a module
Thanx Tom
Can you list the code and I will paste it in a module
Thanx Tom
the code
Sub Macro3()
For Idx = 1 To 3 Step 2
Set OrigSht = Sheets("Sheet" & Idx)
Set DestSht = Sheets("Sheet" & Idx + 1)
DestSht.Cells.Delete
OrigSht.Cells.Copy Destination:=DestSht.Range("A1")
DestSht.Activate
Range("A1") = "Sheet " & Idx + 1
LastRow = Range("A" & Cells.Rows.Count).End(xlUp).Row
myRow = LastRow
While myRow <> 1
Range("G" & myRow + 1).Formula = "=COUNT(G" & Range("A" & myRow).End(xlUp).Row & ":G" & myRow & ")"
Range("I" & myRow + 1).Formula = "=AVERAGE(I" & Range("A" & myRow).End(xlUp).Row & ":I" & myRow & ")"
Range("J" & myRow + 1).Formula = "=AVERAGE(J" & Range("A" & myRow).End(xlUp).Row & ":J" & myRow & ")"
Range("L" & myRow + 1).Formula = "=AVERAGE(L" & Range("A" & myRow).End(xlUp).Row & ":L" & myRow & ")"
Range("N" & myRow + 1).Formula = "=AVERAGE(N" & Range("A" & myRow).End(xlUp).Row & ":N" & myRow & ")"
Range("P" & myRow + 1).Formula = "=AVERAGE(P" & Range("A" & myRow).End(xlUp).Row & ":P" & myRow & ")"
Range("J" & myRow + 3).Formula = "=COUNTIF(J" & Range("A" & myRow).End(xlUp).Row & ":J" & myRow & "," & Chr(34) & "<0" & Chr(34) & ")"
Range("N" & myRow + 3).Formula = "=COUNTIF(N" & Range("A" & myRow).End(xlUp).Row & ":N" & myRow & "," & Chr(34) & "<0" & Chr(34) & ")"
Range("K" & myRow + 2).FormulaR1C1 = "=(R[-1]C[-4]-R[1]C[-1])/R[-1]C[-4]"
Range("O" & myRow + 2).FormulaR1C1 = "=(R[-1]C[-8]-R[1]C[-1])/R[-1]C[-8]"
myRow = Range("A" & myRow).End(xlUp).End(xlUp).Row
Wend
Next
End Sub ASKER
I located the cursor on G15 in sheet1 and ran the macro
Got a compile error
expected:=
Have you tried to run the macro at G15 in sheet1?
Tom
Got a compile error
expected:=
Have you tried to run the macro at G15 in sheet1?
Tom
HI,
On which line of my code does the error appear
Regards
On which line of my code does the error appear
Regards
ASKER
I look at the code after the error and there was no indication as to were the error was located.
Would step into be a way of isolating the error ?
Again, have you tried to run the macro on sheet1???
You should see the same error as i do..... why not try it?
Tom
Would step into be a way of isolating the error ?
Again, have you tried to run the macro on sheet1???
You should see the same error as i do..... why not try it?
Tom
Sorry
it works for me perfectly, could you try to run it step by step
Regards
it works for me perfectly, could you try to run it step by step
Regards
ASKER
Please present the code so that i can paste it and try it
How does one run it step by step .... never did that
Thanx Tom
How does one run it step by step .... never did that
Thanx Tom
The code is always the same
in the Visual Basic Editor, click on a part of the code and then use F8 to run it step by step
(or Menu Debug / Step Into)
Regards
in the Visual Basic Editor, click on a part of the code and then use F8 to run it step by step
(or Menu Debug / Step Into)
Regards
ASKER
I had trouble running the macro.
Got locked up, and had to reset my computer.
After the reset, Did the following:
Right clicked sheet1 tab of supermax file
Selected View code
From the menu, selected Insert then Module
pasted the code in the VBA window
closed the VBA window
Pressed ALT-F8 ..... no macro was present to run????
The macro name should have been there, but wasnt
Can you help me out on this ?
Got locked up, and had to reset my computer.
After the reset, Did the following:
Right clicked sheet1 tab of supermax file
Selected View code
From the menu, selected Insert then Module
pasted the code in the VBA window
closed the VBA window
Pressed ALT-F8 ..... no macro was present to run????
The macro name should have been there, but wasnt
Can you help me out on this ?
No idea what the problem could be
Sorry
Sorry
ASKER
Erased the code in my computer and started over.
pasted the code in the VBA window
This time the macro appeared!!!!
Tried to run in and got this message:
compile error:
Invalid outside procedure
For Idx = 1 to 3 step2 was highlighted
Any suggestions?
pasted the code in the VBA window
This time the macro appeared!!!!
Tried to run in and got this message:
compile error:
Invalid outside procedure
For Idx = 1 to 3 step2 was highlighted
Any suggestions?
HI,
You appear to be missing the Sub line at the start
Sub Macro3()
You appear to be missing the Sub line at the start
Sub Macro3()
ASKER
Somehow i got the macro to run .... without an error
However it did not give me the correct results.
I placed the cursor on g15 in sheet1 and after running the macro, the cursor was at 2a in SHEET4????
No calculations where made in sheet 1
Is the macro designed to do calculations for several blocks of data with one click?
If so, thats not what is required.
The macro should do calculations for ONE block AT A TIME .... that is important
The cursor would be moved to the next block of data and run again .... that's what is required.
If it tries to do several blocks of data with one click, please change the code . One at a time is what is required.
However it did not give me the correct results.
I placed the cursor on g15 in sheet1 and after running the macro, the cursor was at 2a in SHEET4????
No calculations where made in sheet 1
Is the macro designed to do calculations for several blocks of data with one click?
If so, thats not what is required.
The macro should do calculations for ONE block AT A TIME .... that is important
The cursor would be moved to the next block of data and run again .... that's what is required.
If it tries to do several blocks of data with one click, please change the code . One at a time is what is required.
Has the OP provided an sample file for this request (another thread maybe)? I see some modified versions from Experts, but would like to view the original if possible.
@radrick60 -
What is performed by the macro and what is done manually? You seem to have conflicting statements between posts.
@Rgonzo1971 - Patience of a Saint...
@radrick60 -
1. Place the cursor at G15 .... starting point for the macro
2. Calculate the COUNT MUMBERS from G4 to G14 range
3. Move the cursor to I15
4. Calculate the AVERAGE from I4 to I14 range
5. Move the cursor to J15
6. Calculate the AVERAGE from J4 to J14 range
What is performed by the macro and what is done manually? You seem to have conflicting statements between posts.
@Rgonzo1971 - Patience of a Saint...
@raderic60...
1. rgonzo is making all the changes on sheet2 to keep from making a mess of the original data on sheet1. He copies Sheet1 data to Sheet2 and works on it there. An excellent idea while testing! The sample works for me on XL2007.
2. And his macro works for all the data blocks on a worksheet by finding the last row and working up from there. You seem to be indicating you want to select each block separately so i have modified so it asks for the start.
I added a button to Sheet2 to show how it can be used. The button simply runs the AddSummary macro, that asks for the start location and then add the formula.
1. rgonzo is making all the changes on sheet2 to keep from making a mess of the original data on sheet1. He copies Sheet1 data to Sheet2 and works on it there. An excellent idea while testing! The sample works for me on XL2007.
2. And his macro works for all the data blocks on a worksheet by finding the last row and working up from there. You seem to be indicating you want to select each block separately so i have modified so it asks for the start.
'update by Robberbaron @ EE Feb 2013
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("N" & rowEnd + 3).Formula = "=COUNTIF(N" & rowStart & ":N" & rowEnd & "," & Chr(34) & "<0" & Chr(34) & ")"
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]"
End SubI added a button to Sheet2 to show how it can be used. The button simply runs the AddSummary macro, that asks for the start location and then add the formula.
note that i ask for the start row of the data block and then find the end of data block using ColumnA.
'update by Robberbaron @ EE Feb 2013
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("N" & rowEnd + 3).Formula = "=COUNTIF(N" & rowStart & ":N" & rowEnd & "," & Chr(34) & "<0" & Chr(34) & ")"
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]"
End SubASKER
We must be getting closer ...... however the macro will not run.
Like the idea of entering the starting location
Only want to calculate data for one block at a time .... extends the macro use for other applications
First. There are two VBA code listings above .... what is the difference?
Tried running the (both) macros on Sheet1 and Sheet3 .... no calculations resulted
This is how I entered the code:
* Right clicked on Sheet1
*Selected View Code
*From the menu clicked on Insert then Module
* Pasted the code in the VBA window
*Closed the VBA window and got back to Sheet1
* Pressed ALT-F8
* selected "all open worksheets" ... also tried "this worksheet" and super macro.xlsx
*Selected the macro
*clicked on RUN
* Selected G15 (starting point) on Sheet1 .... then OK
No calculations presented on Sheet1
Question. does the xlsx extention mean no macros can be run in this file???
Thanx Tom
Like the idea of entering the starting location
Only want to calculate data for one block at a time .... extends the macro use for other applications
First. There are two VBA code listings above .... what is the difference?
Tried running the (both) macros on Sheet1 and Sheet3 .... no calculations resulted
This is how I entered the code:
* Right clicked on Sheet1
*Selected View Code
*From the menu clicked on Insert then Module
* Pasted the code in the VBA window
*Closed the VBA window and got back to Sheet1
* Pressed ALT-F8
* selected "all open worksheets" ... also tried "this worksheet" and super macro.xlsx
*Selected the macro
*clicked on RUN
* Selected G15 (starting point) on Sheet1 .... then OK
No calculations presented on Sheet1
Question. does the xlsx extention mean no macros can be run in this file???
Thanx Tom
1. the code is the same. my fault for attaching twice.
2. you appear to be entering the code correctly.
3. yes, the workbook must be saved as Macro enabled. XLSM ; but the fact that you can run the macro means it is working prior to save.
4. the macro works fine for me on the test workbook.... see if it does for you.
5. post a copy of your test workbook. macro enabled...
SUPER-MACROv2.xlsm
2. you appear to be entering the code correctly.
3. yes, the workbook must be saved as Macro enabled. XLSM ; but the fact that you can run the macro means it is working prior to save.
4. the macro works fine for me on the test workbook.... see if it does for you.
5. post a copy of your test workbook. macro enabled...
SUPER-MACROv2.xlsm
ASKER
The attached file is macro enabled .xlsm extension
The macro still doesnt work ..... frustrating being so close
Please goto G15 on sheet1 and run the macro.
Results should be per Sheet2
Downloaded your version of SUPER-MACROv2.xlsm and tried the macro
Got a run time error"7"
out of memory
Will try closing the xlsm file and re-open it
Thanx Tom
SUPER-MACRO.xlsm
The macro still doesnt work ..... frustrating being so close
Please goto G15 on sheet1 and run the macro.
Results should be per Sheet2
Downloaded your version of SUPER-MACROv2.xlsm and tried the macro
Got a run time error"7"
out of memory
Will try closing the xlsm file and re-open it
Thanx Tom
SUPER-MACRO.xlsm
ok it works for me on XL2007.
1. You only need the macro code once... not in 3 different modules. no need to paste it everytime.
2. my code asks for the TOP of a data block. i find it easier to find end of data, ratehr than find the top... ie
2.1 run the AddSummary and select A4.
2.2 the macro adds the summary at G15 as this is the gap below the data.
2.3 if really needed, i can change the selection to be the bottom of data block, or maybe anywhere inside data block ? But the blank row at the bottom is harder to check as valid.
3. I added some basic checks on user input.
SUPER-MACRO3.xlsm
1. You only need the macro code once... not in 3 different modules. no need to paste it everytime.
2. my code asks for the TOP of a data block. i find it easier to find end of data, ratehr than find the top... ie
2.1 run the AddSummary and select A4.
2.2 the macro adds the summary at G15 as this is the gap below the data.
2.3 if really needed, i can change the selection to be the bottom of data block, or maybe anywhere inside data block ? But the blank row at the bottom is harder to check as valid.
3. I added some basic checks on user input.
SUPER-MACRO3.xlsm
ASKER
ASKER
Rob:
The macro works !!!!! ... selecting the top (A4) is OK by me
A refinement is required.
I added "NEW SHEET" to the attached SUPER-MACRO.XLSM file which is simply a new sheet with 3 data blocks, and ran the macro.
The calculated results are correct, but the format of the resulting calculations is scrambled pretty bad.
In my initial description of the macro requirements, the calculated data formats were defined. Also Sheet1/2 is an example of the desired format of the calculated data.
Can you add the calculated data format requirements to the VBA code?
What do I have to do for this macro to be available on any file that is opened ?
The reason I ask this, is that I had a macro that created the proper spacing between data blocks (4 spaces). I "hot keyed" the macro SHIFT+CTRL +S.
I used this macro several times over the past week ...... recently I tried it on a new file and it didnt work. Worst yet, I cant find the macro anywhere?
Can you give a clue as to how to "find" the SHIFT+CTRL+S macro?
Dont want this to happen with your calculation macro.
Thanx Tom
The macro works !!!!! ... selecting the top (A4) is OK by me
A refinement is required.
I added "NEW SHEET" to the attached SUPER-MACRO.XLSM file which is simply a new sheet with 3 data blocks, and ran the macro.
The calculated results are correct, but the format of the resulting calculations is scrambled pretty bad.
In my initial description of the macro requirements, the calculated data formats were defined. Also Sheet1/2 is an example of the desired format of the calculated data.
Can you add the calculated data format requirements to the VBA code?
What do I have to do for this macro to be available on any file that is opened ?
The reason I ask this, is that I had a macro that created the proper spacing between data blocks (4 spaces). I "hot keyed" the macro SHIFT+CTRL +S.
I used this macro several times over the past week ...... recently I tried it on a new file and it didnt work. Worst yet, I cant find the macro anywhere?
Can you give a clue as to how to "find" the SHIFT+CTRL+S macro?
Dont want this to happen with your calculation macro.
Thanx Tom
1. by format you mean the colors and bold , % etc ? If so, will need to check the formats and see if they are easily applied. should be.
2. each macro is installed within a module of a specific workbook. you need to find the workbook holding the macro. trial and error ! then create yourself a 'Helper' workbook that you store all the macros in. then load this one workbook that has no data, just macros so you dont lose track of them. (can even be personal.xls that gets loaded automatically)
2. each macro is installed within a module of a specific workbook. you need to find the workbook holding the macro. trial and error ! then create yourself a 'Helper' workbook that you store all the macros in. then load this one workbook that has no data, just macros so you dont lose track of them. (can even be personal.xls that gets loaded automatically)
'original formaula code by Rgonzo1971
Range("G" & rowEnd + 1).Formula = "=COUNT(G" & rowStart & ":G" & rowEnd & ")"
Range("G" & rowEnd + 1).NumberFormat = "0"
FormatBlack Range("G" & rowEnd + 1)
Range("I" & rowEnd + 1).Formula = "=AVERAGE(I" & rowStart & ":I" & rowEnd & ")"
Range("I" & rowEnd + 1).NumberFormat = "0"
FormatBlack Range("I" & rowEnd + 1)
Range("J" & rowEnd + 1).Formula = "=AVERAGE(J" & rowStart & ":J" & rowEnd & ")"
Range("J" & rowEnd + 1).NumberFormat = "0.0%"
FormatBlack Range("J" & rowEnd + 1)
Range("L" & rowEnd + 1).Formula = "=AVERAGE(L" & rowStart & ":L" & rowEnd & ")"
Range("L" & rowEnd + 1).NumberFormat = "0.0%"
FormatBlack Range("L" & rowEnd + 1)
Range("N" & rowEnd + 1).Formula = "=AVERAGE(N" & rowStart & ":N" & rowEnd & ")"
Range("N" & rowEnd + 1).NumberFormat = "0.0%"
FormatBlack Range("N" & rowEnd + 1)
Range("P" & rowEnd + 1).Formula = "=AVERAGE(P" & rowStart & ":P" & rowEnd & ")"
Range("P" & rowEnd + 1).NumberFormat = "0.0%"
FormatRed Range("P" & rowEnd + 1)
Range("J" & rowEnd + 3).Formula = "=COUNTIF(J" & rowStart & ":J" & rowEnd & "," & Chr(34) & "<0" & Chr(34) & ")"
Range("J" & rowEnd + 3).NumberFormat = "0"
FormatRed Range("J" & rowEnd + 3)
Range("N" & rowEnd + 3).Formula = "=COUNTIF(N" & rowStart & ":N" & rowEnd & "," & Chr(34) & "<0" & Chr(34) & ")"
Range("N" & rowEnd + 3).NumberFormat = "0"
FormatRed Range("N" & rowEnd + 3)
Range("K" & rowEnd + 2).FormulaR1C1 = "=(R[-1]C[-4]-R[1]C[-1])/R[-1]C[-4]"
Range("K" & rowEnd + 2).NumberFormat = "0.0%"
FormatBlack Range("K" & rowEnd + 2)
Range("O" & rowEnd + 2).FormulaR1C1 = "=(R[-1]C[-8]-R[1]C[-1])/R[-1]C[-8]"
Range("O" & rowEnd + 1).NumberFormat = "0.0%"
FormatBlack Range("O" & rowEnd + 2)
End Sub
Sub FormatRed(rng As Range)
With rng.Font
.Name = "Arial"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -16776961
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
End Sub
Sub FormatBlack(rng As Range)
With rng.Font
.Name = "Arial"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
End SubASKER
Rob:
Getting closer.
Your calculation macro works great and another previous macro dealing with creating spaces between data blocks is also working.
To have them available all the time, should I select "all open worksheets" or have them in the Personalxlsb file?
Would like to rename the macros .... yours in currently AddSummary and the other is named Modified demo. How would one do that?
To finalize my macro requirements, I want to create a macro using the MACRO RECORDER .
I record the macro from a starting position i.e. G15. The macro runs fine. The problem I have is when I try to run it with the cursor at position G35 ..... it runs the macro at position G15.
I click on Relative mode before i start the macro recorder but it doesnt seem to help. Is there an ndicator somewhere that indicates the mode selected?
As a comment, I think Microsoft did a bad job in structuring the macro generation and creating a structure for saving /using the macros. The HELP information related to macros is close to useless.
Thanx Tom
Getting closer.
Your calculation macro works great and another previous macro dealing with creating spaces between data blocks is also working.
To have them available all the time, should I select "all open worksheets" or have them in the Personalxlsb file?
Would like to rename the macros .... yours in currently AddSummary and the other is named Modified demo. How would one do that?
To finalize my macro requirements, I want to create a macro using the MACRO RECORDER .
I record the macro from a starting position i.e. G15. The macro runs fine. The problem I have is when I try to run it with the cursor at position G35 ..... it runs the macro at position G15.
I click on Relative mode before i start the macro recorder but it doesnt seem to help. Is there an ndicator somewhere that indicates the mode selected?
As a comment, I think Microsoft did a bad job in structuring the macro generation and creating a structure for saving /using the macros. The HELP information related to macros is close to useless.
Thanx Tom
1. to have them avail all the time.... Personal.xlsm note the xlsm. or have a workbook called macros.xlsm
2. to rename macro... change the name of the Sub Addsummary() to Sub MyNewMacro()
3. the macro recorder is GREAT. but only for a starting idea for the macro. the start location is called Activecell. you can reference from that.
4. help has got worse... just go to MSDN rather than the online help.
2. to rename macro... change the name of the Sub Addsummary() to Sub MyNewMacro()
3. the macro recorder is GREAT. but only for a starting idea for the macro. the start location is called Activecell. you can reference from that.
4. help has got worse... just go to MSDN rather than the online help.
ASKER
Rob:
I am so discouraged
Had all 3 macros running correctly, and I changed the name of one of them.
Now nothing works.
When I try to run your macro "AddSummary" , the Run is not bold .... cannot run it.
Seems like I am stuck in the debugger????
Can you help me out of this mess
Thanx Tom
I am so discouraged
Had all 3 macros running correctly, and I changed the name of one of them.
Now nothing works.
When I try to run your macro "AddSummary" , the Run is not bold .... cannot run it.
Seems like I am stuck in the debugger????
Can you help me out of this mess
Thanx Tom
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Point 3 of your Macro Requirements
* The row locations for the macro execution will NOT be the same
I will ask the same question I asked, without answer, in your previous question "How are you identifying the location of these rows?"
In addition, where are you planning to execute the macro from to have it work on the 50 sheets? Are they separate sheets within a single workbook or multiple workbooks?
Thanks
Rob H