• 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 For Macro to Create Pop up for Certain Condition

R

Reggie Jardin

Member
Hi,

I need a macro that when button press will create either a Pop UP (Msg Box) or a New Excel Sheet base on a given condition of a certain column like counting the number of SH with less than or equal to Zero then it will pop up (msg box) or create a new excel that will give those categories which are less than or equal to 0, I attached the excel sheet for the requirement, sheet 1 is the base file and the second sheet must be the output of the macro either in new excel sheet or a Pop up (Msg Box).

Many Thanks Guys.
 

Attachments

  • Macro for Pivot Certain Cond.xlsx
    11.5 KB · Views: 3
HI,

That is what exactly what im looking for, but it should give the breakdown also of each, not only total.. In user Form it should be,

Category 16 SH That is less than or Equal to 0
ANDROMEDA 1
ASTEROID 1
MARS 1
OMEGA 1
OMNICRON 1
RIEGEL 1
Grand Total 6
 
Last edited by a moderator:
Belleke said:
Like this?
See attached.
Click to expand...

Basically, there should be a breakdown of how many are equal and less than 0
in each and everything in column P, as that is the most important, then finally in the button the total of it..

Note: That its should show the pivoted total of each category, as I tried to add another line, it;s adding another line also of same category if

Mars are having 2 value that less that 0r equal to 0 it should show

Mars = 2

not

Mars = 1
Mars = 1
 
Last edited:
Hi Belleke,

Sorry to bother you again,

Actually today I run your Macro but the things is I run it in a data with 1048574 number of rows hence i received a run time error '6' overflow

and when i click debug button it goes here

count = Application.WorksheetFunction.CountIf(Sheets("STORE COUNT OF NO STOCK").Range("A2:A" & lastRow), Sheets("STORE COUNT OF NO STOCK").Cells(i, 1))

I think it is because of that column A and B which is writing all the count and overlapping, it is possible to remove that? and just do the calculation like in D and E only..No Need Column A and B

:(
 
Last edited:
Hi Reggie,
Change this line of code
Code: 
Dim count As Integer, i As Integer, j As Integer
to
Code: 
Dim count As Long, i As Long, j As Long
hope this helps.
 
Belleke said:
Hi Reggie,
Change this line of code
Code: 
Dim count As Integer, i As Integer, j As Integer
to
Code: 
Dim count As Long, i As Long, j As Long
hope this helps.
Click to expand...


Belleke said:
Hi Reggie,
Change this line of code
Code: 
Dim count As Integer, i As Integer, j As Integer
to
Code: 
Dim count As Long, i As Long, j As Long
hope this helps.
Click to expand...


Unfortunately This macro is not working when dealing huge data..
 
I tried to reduce the nos of rows from 1M into just 1K but the macro just keep on running for long time..

Can someone help me with this, badly needed...
 
I tried to run in 300K lines and still showing overflow when tried to debug it goes on this line j = j + 1

output came but some lines did not total it repeating as other line not total..
 
You must log in or register to reply here.
Back
Top