# 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
###### Who is Participating?

Commented:
only thing i can think of is to change to the visual basic window (Developer / VisualBasic) then find the Reset button, a blue square across top.
0

Finance AnalystCommented:
I assume this is a follow on from the previous question, identifying number of rows above a point where a calculation is needed.

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
0

Author Commented:
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
0

Finance AnalystCommented:
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
0

Author Commented:
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
0

Commented:
Hi,

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
``````
Regards
0

Finance AnalystCommented:
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?
0

Commented:
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
0

Author Commented:
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
0

Commented:
HI,

Could you tell me where are the wrong calculations?
SUPER-MACROv1.xlsm
0

Author Commented:
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
0

Finance AnalystCommented:
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
0

Commented:
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
0

Author Commented:
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
0

Commented:
Hi,

The Code is in Module 1

Regards
0

Author Commented:
Sorry, but I am low tech guy ...... where is module 1?

Did you try to run the macro on sheet1?
0

Commented:
Goto View / Macros / View Macros

Choose
'SUPER-MACROv2.xlsm'!Module1.Macro3

And Run
0

Author Commented:
Followered your instructions and couldnt find it

Can you list the code and I will paste it in a module

Thanx  Tom
0

Commented:
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
``````
0

Author Commented:
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
0

Commented:
HI,

On which line of my code does the error appear

Regards
0

Author Commented:
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
0

Commented:
Sorry

it works for me perfectly, could you try to run it step by step

Regards
0

Author Commented:
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
0

Commented:
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
0

Author Commented:
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 ?
0

Commented:
No idea what the problem could be

Sorry
0

Author Commented:
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?
0

Commented:
HI,

You appear to be missing the Sub line at the start

Sub Macro3()
0

Author Commented:
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.
0

PresidentCommented:
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.

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...
0

Commented:
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

Dim mySel As Range
Set mySel = Application.InputBox(prompt:="Select the first cell of a data block", Title:="Select start", Type:=8)

End Sub

'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 Sub
``````

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.
0

Commented:
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

Dim mySel As Range
Set mySel = Application.InputBox(prompt:="Select the first cell of a data block", Title:="Select start", Type:=8)

End Sub

'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 Sub
``````
0

Author Commented:
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
0

Commented:
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
0

Author Commented:
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

Got a run time error"7"
out of memory

Will try closing the xlsm file and re-open it

Thanx    Tom
SUPER-MACRO.xlsm
0

Commented:
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
0

Author Commented:
0

Author Commented:
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
0

Commented:
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)
0

Commented:
``````
'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
.Underline = xlUnderlineStyleNone
.Color = -16776961
.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
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.ThemeFont = xlThemeFontNone
End With
End Sub
``````
SUPER-MACRO4.xlsm
0

Author Commented:
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
0

Commented:
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.
0

Author Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.