• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help In Modifying This Macro Slightly Please

E

Excel-Access

Member
Dear Excel Community - I had placed this in Excel General but realise afterwards that this relates to macro and may be should be placed here - so apologies as I am fairly new to the forum.

Attached is a wonderful macro that Luke created. I need the following modification please

◘On the Analysis Tab, can we in Column I have an embedded table that will allow us to change the parameters in the macro:

ElseIf Abs(WorksheetFunction.Sum(c.Offset(0, 5))) >= 1000 And _
Abs(WorksheetFunction.Sum(c.Offset(0, 6))) >= 0.01 Then

I may need to change the 1000 or 0.01 easily

◘Also have a RUN Button to have this working work

Thanks a million
 

Attachments

  • Example_DataAnalysis_Updated.xlsm
    51.7 KB · Views: 1
Narayank

On testing, I have realised there is a problem, although the macro button is working, I notice that even if you delete Rows from Data Sheet but click the Run button, the historical data in Analysis Tab is still showing

Look at the spread sheet in the Data Tab I only Have France, but when I click on Macro button other countries are still coming up even though I deleted them.

Can you or anyone in this community please advise and fix my friend?

Thanks
 

Attachments

  • Example_DataAnalysis_Updated2.xlsm
    45.1 KB · Views: 1
Hi ,

See if this is OK. Before the data is transferred to the Analysis tab , the region of existing data is cleared.

Narayan
 

Attachments

  • Example_DataAnalysis_Updated2.xlsm
    42.4 KB · Views: 3
Narayan

May I ask you a quick question please. I tried opening a new work book and copied the macro and also the Data And Analysis Tab but when I click Copy Data button, I am getting Run Time Error 13. Can you please advise as I will need to produce this work on a different file. I must be doing something silly.

I have attached the file my friend.

Regards
 

Attachments

  • Book1 Test.xlsm
    46 KB · Views: 1
Hi ,

One point straightaway :

Define two named ranges as follows :

Factor_1 referring to =Analysis!$I$1

Factor_2 referring to =Analysis!$I$2

To do this , select the range H1:I2 , and click on Create From Selection.

Narayan
 
Narayan

That has worked - you are so kind and helpful.

One thing I do notice though is that in the Analysis Tab, if I have cleared all the entities and not the Header in Row 2, when I click on the Copy Button the Header Titles Disappear - Example Column E is Entity, F is Category - this disappears, should I be changing something also.

Your genius advice will be appreciated.
 
Hi ,

Sorry , that was my mistake ; please look for the following line in the code :

Range("A3:G" & .Cells(.Rows.Count, "E").End(xlUp).Row).ClearContents

and replace it with the following :

Range("A3:G" & .Cells(.Rows.Count, "E").End(xlUp).Offset(1).Row).ClearContents

What this does is look for the last row from the bottom of the worksheet , and then go down one row further down. When only the header row is present , this will clear the row just below the header row , instead of the header row itself !

When ever data is present , it does not matter which one you use , but when data is not present , it makes a difference.

Of course , reinsert the header row before you try out anything.

Narayan
 
Narayan or Excel Users

May I ask a question please.

In the attached file, what do I need to alter in the macro so it only works in the active sheet that has the macro. I notice if I open a new workbook and click the macro button, I seethe names of other Excel files.

Correct me if I am wrong in that it already is set to current sheet only and not others.

Thanks
 

Attachments

  • Example_DataAnalysis_Updated2.xlsm
    45.1 KB · Views: 1
Hi ,

As such you do not need to change anything , though you can qualify the worksheet names with ThisWorkbook ; the reason why you see the names when you click the macro button is because there is a drop-down list named Macros in ; if this is selected to All Open Workbooks , then all macros will be shown ; if you select only one workbook after making that workbook active , then only the macros pertaining to that workbook will be shown.

For instance , suppose you have the workbook named Example_DataAnalysis_Updated3.xlsm open ; now you open a new workbook which is named say Book2 ; if you make Book2 active , and check the macros by selecting Book2 in the drop-down , you will not see anything.

Narayan
 
Narayan

I am still having an issue with the header disappearing. In the attached spread sheet I deleted Rows 3 to 24 that had data. When I click the Copy Button, the Headers in Row 2 disappears.

Please tell me what I am doing wrong?

Regards
 

Attachments

  • DataAnalysis_Updated_26.01.14.xlsm
    37.1 KB · Views: 1
Hi ,

That change , about adding the part Offset(1) in between , was not present in the code. I have added it.

Narayan
 

Attachments

  • DataAnalysis_Updated_26.01.14.xlsm
    39 KB · Views: 2
Narayan - Thanks for your considerable help and patience.

Can I confirm with you. Currently in Factor_2 the number 0.1 equates to 1% , if I format this to percentage I am geeting 10%. In order to help users, how can I change this to show 1% or 2.5% and still calculate correctly?

Thanking you in advance my friend.
 
Narayan or Excel Community

Is it possible to modify the macro so that when you click on the Copy Data Button it still groups the Entity as it currently does - Column A but will sort the Variance Column F from Largest to Smallest Value please?
 

Attachments

  • DataAnalysis_Updated_26.01.14.zip
    35 KB · Views: 3
Narayan

I notice a small issue, I have added another country and Column A is not giving the desired result although Column F is.

However what I wanted is to also have in Column A, France grouped together and Germany grouped together but still having Column F sorted by ascending order.

Please advise my friend.

Thanks
 

Attachments

  • DataAnalysis_Updated_29.01.14.zip
    45.2 KB · Views: 1
You must log in or register to reply here.
Back
Top