Solved

CREATE AN EXCEL MACRO

Posted on 2014-01-29
45
376 Views
Last Modified: 2014-02-07
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
0
Comment
Question by:radrick60
  • 19
  • 12
  • 8
  • +2
45 Comments
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39817943
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 Comment

by:radrick60
ID: 39818022
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39818164
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 Comment

by:radrick60
ID: 39819193
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
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 39820091
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

Open in new window

Regards
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39820287
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
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 39820413
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 Comment

by:radrick60
ID: 39820504
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
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 39820515
HI,

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

Author Comment

by:radrick60
ID: 39821083
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39821308
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
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 39821439
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 Comment

by:radrick60
ID: 39822359
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
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 39823280
Hi,

The Code is in Module 1

Regards
0
 

Author Comment

by:radrick60
ID: 39823315
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?
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 39823324
Goto View / Macros / View Macros

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

And Run
0
 

Author Comment

by:radrick60
ID: 39823327
Followered your instructions and couldnt find it

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

Thanx  Tom
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 39823338
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 

Open in new window

0
 

Author Comment

by:radrick60
ID: 39823390
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
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 39823497
HI,

On which line of my code does the error appear

Regards
0
 

Author Comment

by:radrick60
ID: 39824133
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
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 39826589
Sorry

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

Regards
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:radrick60
ID: 39826619
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
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 39826633
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 Comment

by:radrick60
ID: 39826751
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
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 39826756
No idea what the problem could be

Sorry
0
 

Author Comment

by:radrick60
ID: 39826769
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
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 39826775
HI,

You appear to be missing the Sub line at the start

Sub Macro3()
0
 

Author Comment

by:radrick60
ID: 39826817
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
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39831382
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 -
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
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39831427
@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.
'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 Sub

Open in new window


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
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39831429
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 Sub

Open in new window

0
 

Author Comment

by:radrick60
ID: 39831769
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
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39831785
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 Comment

by:radrick60
ID: 39832105
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
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39834113
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 Comment

by:radrick60
ID: 39835270
0
 

Author Comment

by:radrick60
ID: 39835311
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
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39835323
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
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39835360
                
    '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 Sub

Open in new window

SUPER-MACRO4.xlsm
0
 

Author Comment

by:radrick60
ID: 39838706
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
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39838776
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 Comment

by:radrick60
ID: 39840934
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
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 500 total points
ID: 39841048
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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Citrix XenApp, Internet Explorer 11 set to Enterprise Mode and using central hosted sites.xml file.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now