1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Sum on every Blank Cell of amount in another column, If column have perticular text,

Discussion in 'VBA Macros' started by Chirag R Raval, Nov 1, 2018.

  1. Chirag R Raval

    Chirag R Raval Member

    Messages:
    661
    Dear & Respected experts,

    I have a file (daily new file), with subtotal on amount,
    require sum of every data block , on every blank cell, if
    another column have particular word in column..

    hope below image described well my requirement.

    how to dynemic sum.png

    found blank cell on column "K" & put there sum or sumproduct formula there
    for sum of amount column "G", for that block only , if column "K" have work "Over Due"
    & only sum amount of related that word only.

    Hope there are some vba solution found.

    Regards,

    Chirag Raval
  2. vletm

    vletm Excel Ninja

    Messages:
    4,415
    Chirag R Raval
    Rebus ?
    With Your given information...
    test this:
    Code (vb):

    Sub Do_It()
        With Application
            cm = .Calculation
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
        End With
        With ActiveSheet
            g = 2
            k = 0
            Do
                If .Cells(g, 11) = "Over Due" Then k = k + .Cells(g, 7)
                If .Cells(g, 11) = Empty Then
                    .Cells(g, 11) = k
                    k = 0
                End If
                g = g + 1
            Loop Until .Cells(g, 7) = ""
        End With
        With Application
            .Calculation = cm
            .ScreenUpdating = True
        End With
    End Sub
     
  3. Chirag R Raval

    Chirag R Raval Member

    Messages:
    661
    Dear Sir @vletm

    Thank you very much sir ..
    I run your code...but.... its stuck on Over Flow error.

    overflow.png on g.png

    Please advise.

    Regards,

    Chirag Raval
  4. vletm

    vletm Excel Ninja

    Messages:
    4,415
    Chirag R Raval
    Still Rebus ...
    If You offer screenshots from something
    ... it's challenge to find out 'what-else-would-be-there'!
    With those #1 screenshot data - it should work.
    G-column has only numbers!
    Only You could know - what would be there?
  5. Chirag R Raval

    Chirag R Raval Member

    Messages:
    661
    Dear Sir @vletm ,

    I already change macro as my modified file as (g,11) (g,7) for adjust code catch proper columns but i think it goes beyond range...there are need to stop macro on last row..also , though your code perfectly added values of overdue in variable "k" , there are no "k"'s value entered in blank cells. overflow.png

    Please help..

    Regards,
    Chirag Raval
  6. vletm

    vletm Excel Ninja

    Messages:
    4,415
    Chirag R Raval
    As I already wrote in #4 ...
    If You offer a screenshot, which data seems to be okay, it's challenge to help!
    If G-column has empty cell then that would handle as zero.
    What is G-column cells value then 'challenge' occurs?
  7. Chirag R Raval

    Chirag R Raval Member

    Messages:
    661
    Dear Sir @vletm,

    Thank you very much, Its working Now, Due to always Option Explicit" , overflow error due to variable 'g' i wrongly declared as integer i change it to type long... + i added with 'activesheet.usedrange. to loop not to go beyond range.

    can you help for achieve Grand Total At last of columns "G" & "K"? at yellow highlighted?
    can cells contain word Over Due + its Amount background filling Yellow?
    can put comment with Total Overdue amount on each cell if mouse hover over any overdue cell of that block (for that buyer).

    can grand total.png
    code.png

    Hope there are way.

    Regards,

    Cirag Raval
  8. vletm

    vletm Excel Ninja

    Messages:
    4,415
    Chirag R Raval
    So, You made 'some modifications' which made mess and
    You've asked why 'my code' didn't work ... hmm?
    Did You answer to my questions?
    Now, I see that there are many 'modifications' which ... no need!
    I won't/cannot use that code.

    You asked to add 'some two values' in the end ...
    K-column value would be something You've asked.
    G-column value ... would show Sum of 'Not Due'.
    Only You'll know ... what are You looking for.

    Attached Files:

  9. Chirag R Raval

    Chirag R Raval Member

    Messages:
    661
    Dear Sir @vletm ,

    Sorry Sir, I tested as per yours, Post No 4, Its working on that time ..Really Sorry.(Actually my post No 3's screen shot of vertically scrolled sheet, & 1 column variation compare to first screen shot that's my mistake)

    Now, At exact last cell below data , on column "K", how to subtotal of founded above all Overdue amount ?
    Regards,

    Chirag Raval
  10. vletm

    vletm Excel Ninja

    Messages:
    4,415
    Chirag R Raval
    I already added there two values.
    If those would show something else then
    what is needed value somewhere then use that my file?
  11. Chirag R Raval

    Chirag R Raval Member

    Messages:
    661
    Dear Sir @vletm,

    Sorry for not see your valuable code in your attached file.
    I test it its working fine.

    I am also sorry for not clearly explain the situation & requirement.

    I attached my file with your code.
    My file has subtotaled on Amount.
    There are just require "Over Due" Amt not "Not Due" at every blank cell of column "k". for that buyer & just that all Buyer's "Over Due" Amount's grand total at Exact column"k"'s end. (Grand Total of "Overdue" at end.) .

    when i run your code there are seen "type mismatch" displayed

    runtime.png
    my declared variables are seem ok , i already try to change variables "g_max" & "k_max" as variant but i can not figure our what excel want.


    Please help.

    Regards,

    Chirag Raval

    Attached Files:

  12. vletm

    vletm Excel Ninja

    Messages:
    4,415
    Chirag R Raval
    1) when i run your code there are seen "type mismatch" displayed ...
    > G502 has many something ... not number, not empty
    = You should take care Your data
    > If You modify something then You would know what ... not me or how?
    = eg if You declare something then You have to do as Excel needs,
    sometimes 'seems ok' is not enough!
    Hint! Do modifications one-by-one and test everything after that.
    There were 'typo' which even prevent to run that macro!
    I solved those 'my way'.

    2) There are just require "Over Due" Amt not "Not Due" at ...
    Why You couldn't answer to my question WHAT?
    My file has less rows - less calculations - and so on.
    If You cannot give clear short answer,
    then I cannot change my code.

    Attached Files:

  13. Chirag R Raval

    Chirag R Raval Member

    Messages:
    661
    Dear Sir @vletm

    Thank you very much for your kind efforts. you code runs like a charm.
    every thisng its give as desired

    sorry for i can not understand what you want about in every your post.

    I can now put 3rd critera "Due" any where onthe sheet which found in
    aboput 13 cells in column "k".

    with help of this code now i can deistribute amounts in 3 critera "Due", "Over Due" & "Not Due"

    again thank you very much for your kind effort.

    Regards,

    Chirag Raval
  14. Chirag R Raval

    Chirag R Raval Member

    Messages:
    661
    Dear Sir @vletm

    How can it possible without VBA? if select blank cells,through F5 method in column "K", then put formula there like Sum Product or Sum if Or Sumifs, for dynamic range of Amount column "G" if column "K" have contain "Over Due"?

    Regards,

    Chirag Raval
  15. vletm

    vletm Excel Ninja

    Messages:
    4,415
    Chirag R Raval
    All can make also without any VBA!
    All those can do also manually.
    Sometimes it would better to think if needs Excel at all?
    Sometimes it would better to use paper and pencil.
  16. Chirag R Raval

    Chirag R Raval Member

    Messages:
    661
    ear Sir @vletm,

    Please Sir, don't misunderstood.

    I desired to perform this as manually on sheet due to i want to teach my colleagues & any one who can't understand VBA way, & struggling to job done.

    if i also know this method that how can it be manual. I just want to help him ...Like You..Help... without any knowlege ego..

    I also search on web for about how to perform on multiple dynamic ranges or if any formula way is there?, but ...can't get as per required.

    My post no 14, I just want to ask that if every blank cells on column "K" select by F5, then how to put formula there for every dynamic range blocks on column "G" & "K"., Control +Shift+ Enter is the way but how to set dynamic range for that formula?

    Whole thing is stuck on Dynamic-Ness of range on numbered columns.

    Please guide.

    Regards,

    Chirag Raval
  17. vletm

    vletm Excel Ninja

    Messages:
    4,415
    Chirag R Raval
    I desired to perform this as manually on sheet due to i want to teach my colleagues & any one who can't understand VBA way, & struggling to job done.
    Sometimes VBA would be more understandable than some formula-solutions.
    You could do things more like manually as step-by-step.

    With formulas ... if there are many of those ... one missed click in somewhere in sheet and that formula could be missed.
    Some weeks later, if lucky someone else would notice it ... or not.
  18. Chirag R Raval

    Chirag R Raval Member

    Messages:
    661
    Dear Sir,

    Thank you..

    But that's the matter of luck of user that missing click (even select blanks from F5 method) but more possibility is to precisely select required cells if work with responsibleness & seriousness by user.

    If working strategy there, then this is understandable that whole depend on user that how it can be done as guided.

    Hope your little help.

    Regards,

    Chirag Raval
  19. vletm

    vletm Excel Ninja

    Messages:
    4,415
    Chirag R Raval
    If You have sheet which has eg 50*50 cells numbers.
    Which would be more effect way to select needed 173 cells?
    Manually one-by-one or with VBA?

    Manually:
    ... after 169 selected cells, user make mistake
    ... and user needs to start from the begin -> nerves!
    ... next time there would be 146 cells to select and so on ...
    VBA:
    ... do 'once' needed correct code
    ... run it
    I've tested both ways --- and nowadays, I'll use VBA.

    Of course,
    if that needs to make only once-in-the-lifetime,
    then manually could be shorter way to do it.
  20. Chirag R Raval

    Chirag R Raval Member

    Messages:
    661
    Dear Sir @vletm

    I agree sir,with your post no 19, that VBA can also check about typos.. & i already prefer VBA way....but.....can it be possible through Mannually is some simple steps?

    If we sure that there are no any typos in any cells of amount column "G",
    then how to put formula on every selected blank cells?

    I have 2 screen shots for your reference with selection

    (1) SELECT ONLY BLANKS THROUGH "F5"
    SELECT ONLY VISIVLE BLANKS.png

    (2) EXPAND WHOLE RANGE BY CLICK ON BUTTON 3

    EXPAND SUBTOTAL AT LEVEL 3 BY BUTTON CLICK.png

    Please guide..what can we do there?

    Regards,

    Chirag Raval
  21. vletm

    vletm Excel Ninja

    Messages:
    4,415
    Chirag R Raval
    I won't use term like 'simple',
    because that means many time double work!

    I could give an idea, which I haven't test nor I won't use myself:
    1) You could try to copy somewhere needed formula eg manually
    2) do Your 'empty selection' as Your (1)
    3) paste to correct range ... hopefully, it would paste only to those visible cells
    4) expand all needed rows visible, as Your (2)
    ---- if mess, then use Your backups to get all as before paste.
    > If this would work
    ... then there would be always some risks eg paste to wrong column.

    Your 'what can we do there?' means someone there, not include me or how?
  22. Chirag R Raval

    Chirag R Raval Member

    Messages:
    661
    Dear Sir,

    Okay , So that Ctl+Shift+Enter work only continues range, then there are
    No other safe way to do this thing manually or formula ways.

    Hope there are suddenly strike some trick in updated future anytime on anyone's mind..

    Till then you magic code do its job forever.

    Thank you very much sir for your co-operation and your in-valuable help regarding this thread.

    Regards,

    Chirag Raval

Share This Page