+ Reply to Thread
Results 1 to 28 of 28

Formula stop when seeing a blank cell

  1. #1
    Forum Contributor
    Join Date
    09-09-2009
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    185

    Talking Formula stop when seeing a blank cell

    Hello,

    Hi have a formula:

    Please Login or Register  to view this content.
    What I am trying to do get this formula to calculate and total the year that it sees. But, when the formula hits a blank cell I need it to stop. I thought I had it here but no luck. Anyone have an idea of what I'm doing wrong??

    Thank you all in advance.
    Last edited by whatsmyname; 07-07-2010 at 10:10 PM.

  2. #2
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: Formula stop when seeing a blank cell

    Try this array formula

    =SUM(--(C8:INDEX(C8:C3322,MIN(IF(C8:C3322="",ROW(C8:C3322)-MIN(ROW(C8:C3322)))))=2006))

    This is an array formula.
    This means that you use Ctrl-Shift-Enter to commit the formula, not just Enter (array Enter it). Excel will put curly brackets around the formula in the formula bar, you don#t do this.
    If you need to change the formula at any time, you must array Enter it again.

  3. #3
    Forum Contributor
    Join Date
    09-09-2009
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    185

    Re: Formula stop when seeing a blank cell

    Hey Bob,

    Thanks for your help!

    I do not unserstand the formula at all. I am somewhat of a begginer. I did copy and past it though and all I get is #VALUE! as my result. Am I not doing something right?

  4. #4
    Forum Contributor
    Join Date
    09-09-2009
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    185

    Re: Formula stop when seeing a blank cell

    This is the original formula that I have that works great.

    Please Login or Register  to view this content.
    The problem is, is when I insert a couple of rows like I need to do, it still calculates and totals the whole column.

    Instead I would rather it stop on a blank row or cell.

  5. #5
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Formula stop when seeing a blank cell

    You could try this:
    In some hidden cell, say Z1 : ="C8:C"&If(IsError(Match(0,C8:C3702,0)),3702,Match(0,C8:C3702,0))
    then your formula could be
    =SUMPRODUCT(--(YEAR(Indirect($Z$1))=2006))

    [/SIZE]
    Last edited by shg; 07-05-2010 at 01:07 AM.
    Foxguy

    Remember to mark your questions [Solved] and rate the answer(s)
    Forum Rules are Here

  6. #6
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: Formula stop when seeing a blank cell

    Quote Originally Posted by whatsmyname View Post
    Hey Bob,

    Thanks for your help!

    I do not unserstand the formula at all. I am somewhat of a begginer. I did copy and past it though and all I get is #VALUE! as my result. Am I not doing something right?
    Did you array-enter it?

  7. #7
    Forum Contributor
    Join Date
    09-09-2009
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    185

    Re: Formula stop when seeing a blank cell

    Quote Originally Posted by Bob Phillips View Post
    Did you array-enter it?
    Yes I did.

  8. #8
    Forum Contributor
    Join Date
    09-09-2009
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    185

    Re: Formula stop when seeing a blank cell

    Quote Originally Posted by foxguy View Post
    You could try this:
    In some hidden cell, say Z1 : ="C8:C"&If(IsError(Match(0,C8:C3702,0)),3702,Match(0,C8:C3702,0))
    then your formula could be
    =SUMPRODUCT(--(YEAR(Indirect($Z$1))=2006))

    [/SIZE]
    I do not know if I was doing something wrong but this did not work either.

  9. #9
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Formula stop when seeing a blank cell

    Hi whatsmyname;
    I did not test my solution.
    I just assumed that an empty cell would be found with Match(0,.....). I was wrong. If you put a zero in the cell then Match(0,....) will find it, but I'm guessing you don't want to do that.

    I'm trying to find a formula right now that will find the empty cell. A macro can easily find it, so if you wanted to have a macro automatically find it for you when you insert a row, I could easily write that. The macro could put a 0 in the cell then the Match(0,.....) would find it.

    Let me know if you would like a macro. Otherwise I'll keep trying to find a formula that works.

  10. #10
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Formula stop when seeing a blank cell

    Hi whatsmyname;

    I just thought of a way to make it real easy for you.
    If you put this into the sheet's module (right mouse click the sheet's tab and select "View Code")
    Please Login or Register  to view this content.
    It will always put the correct address into Z1 with one exception, if Row 9 is empty. If this is a possibility, then the macro can be changed to accommodate it
    Then this formula should work.
    =SUMPRODUCT(--(YEAR(Indirect($Z$1))=2006))
    I don't work with SUMPRODUCT, so I'm not possitive, but if it worked before, then it should work using Indirect().

  11. #11
    Forum Contributor
    Join Date
    09-09-2009
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    185

    Re: Formula stop when seeing a blank cell

    Hi foxguy,

    Thank you for your help. I greatly appreciate it. I'm not sure if I did it right, but I did copy and paste the macro directly into the workbook VBA code. Then I took your formula and pasted it where I wish my result to be. It still did not work though. I could be doing something wrong though.

    If you'd like I could attach a sample workbook that way you could see what I am doing wrong??

    Let me know if you think that will help.

    Thank you so much for your help.

  12. #12
    Forum Contributor
    Join Date
    09-09-2009
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    185

    Re: Formula stop when seeing a blank cell

    I went ahead and attached a workbook sample.

    Once you open it, Click on the DatatoCopy tab and copy the data that is in there.

    Then click on the Current Project tab and click on cell A8 and paste the data in there.

    Then on the Toolbar at the top you'll see a smiley face button, click on that. It will give you an error. I have a bug and am not sure how to fix that so just click end now.

    Then you'll see that I have an accumalation of dates. That all is working good. But, you'll notice that everything is seperated by duplicate locations.

    I only want to total the dates by those individual locations. Meaning, that if I have a group of four and 2 of the dates are 2006 and 2 are 2010. Then I should only show 2 in the 2006 section and 2 in the 2010 section.

    I hope I explained that well enough. I am very new to writing VBA, so if you see alot of mess and stuff, please understand. This is just a sample workbook as well, so I am also using this workbook to test and learn. That way I don't make a mess out of my final result.

    Thank you for your help. I expertiece is greatly appreciated,
    Attached Files Attached Files

  13. #13
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Formula stop when seeing a blank cell

    Hi whatsmyname;

    I'm looking at your workbook now. I don't see a smiley face in a toolbar, and I don't see anywhere in your code where you added a menu selection. What macro is the smiley face supposed to run?

    Also, I see that you put my macro in the wrong module. You put it into "ThisWorkbook". It should go in the Sheet module of the sheet you want to SUMPRODUCT on.
    If you want it on every sheet then you can leave it in ThisWorkbook, but it should then be:
    Please Login or Register  to view this content.

  14. #14
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Formula stop when seeing a blank cell

    Hi whatsmyname;

    I have attached a workbook.
    On sheet "Current Project" I found your SUMPRODUCT formula in A7. I put my macro in the correct place and changed it so that it puts my formula into A4. I also changed the formula in A7 to use it.

    Now you can test it by inserting a row anywhere in the data, and you should see A7 adjust accordingly.

    I did not look at anything else in the sheets or macros, only the SUMPRODUCT formula in A7. This was the subject of your original message. If you like the solution, then mark the thread "Solved" and rate the answer if you liked it.

    If you have more problems or questions, please start another thread.
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    09-09-2009
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    185

    Re: Formula stop when seeing a blank cell

    Quote Originally Posted by foxguy View Post
    Hi whatsmyname;

    I have attached a workbook.
    On sheet "Current Project" I found your SUMPRODUCT formula in A7. I put my macro in the correct place and changed it so that it puts my formula into A4. I also changed the formula in A7 to use it.

    Now you can test it by inserting a row anywhere in the data, and you should see A7 adjust accordingly.

    I did not look at anything else in the sheets or macros, only the SUMPRODUCT formula in A7. This was the subject of your original message. If you like the solution, then mark the thread "Solved" and rate the answer if you liked it.

    If you have more problems or questions, please start another thread.
    This worked. I thank you for your help. It still did not work like I needed it too though. The reason for that though is because I didn't think about telling you specifically which Macro to run and I jsut thought that the smiley face would be on yours. That was my fault, sorry. I'll re-attach your workbook and before you insert any rows select all of the data and then run the "seperate" macro. Then you should see exactly what I am trying to do. You solution worked very well and I tried to tweak the formula just a little bit more to get it to do what I needed it to do. But, because of my ignorance I didn't succeed. If you could look at this one more time for me I please, I would greatly appreciate it.

    Thank you!!
    Attached Files Attached Files
    Last edited by whatsmyname; 07-06-2010 at 08:44 PM.

  16. #16
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Formula stop when seeing a blank cell

    Hi whatsmyname;

    I'm attaching the file with Seperate() improved. You should see it run much faster, and it will appear to all happen at once, not one Insert at a time. When you run it, you will see a spinning circle while it's running, but you won't see the sheet changing at each Insert.

    1) I put my formula in column "L" (the formula in L5 is a dummy as as placeholder)
    2) I changed the formulas in A5:E5
    3) Seperate will put my formula in column "L" for each set of inserted rows. You will see it when you run Seperate.
    4) I put a lot of notes in Seperate to help you improve your programming skills.
    5) When you copy Seperate into your real workbook, make sure that the addresses are correct in the section between the '******************
    6) You do not need to select all the data on Current Project. I put code into Seperate so you can run it from anywhere in the workbook.

    This was an interesting effort. I had never split data up like that and tried to sumproduct on the different sections.
    Attached Files Attached Files

  17. #17
    Forum Contributor
    Join Date
    09-09-2009
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    185

    Re: Formula stop when seeing a blank cell

    Thank you so much Foxguy!!!

    It works beautifully! I was beginning to think that I was asking for something that wasn't possible. I really appreciate all your help and time. I somewhat understand what you did to the seperate code. I think you were making my seperate into ranges themselves. But, alot of it still looked like a different language to me, LOL. I really appreciate all your notes and insight. I can only hope that someday my knowledge will be as vast as yours. You are truely skilled. I'm self teaching myself, so guidance like this is really really helpful. I do not understand the function methods at all though, LOL. I also need to have the prices automatically totaled, but I am going to take what I have learned and try to solve that myself. If I have trouble, I'll definately be posting another thread, LOL. Once again, thank you so much for you time and help, you are a great teacher.

    Thank you!!!!

  18. #18
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Formula stop when seeing a blank cell

    You're welcome.

    I'd appreciate you rating my answer, by clicking the blue scales next to the message # of the answer you liked.

  19. #19
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: Formula stop when seeing a blank cell

    Quote Originally Posted by whatsmyname View Post
    Hey Bob,

    Thanks for your help!

    I do not unserstand the formula at all. I am somewhat of a begginer. I did copy and past it though and all I get is #VALUE! as my result. Am I not doing something right?
    Did you array-enter it as I explained?

  20. #20
    Forum Contributor
    Join Date
    09-09-2009
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    185

    Re: Formula stop when seeing a blank cell

    Yes I did. And it put the squiqqley brackets around the formula and it still gave me that #VALUE! error.

  21. #21
    Forum Contributor
    Join Date
    09-09-2009
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    185

    Re: Formula stop when seeing a blank cell

    Yes sir foxguy. Already taken care of!

  22. #22
    Forum Contributor
    Join Date
    09-09-2009
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    185

    Re: Formula stop when seeing a blank cell

    Hey foxguy,

    I am trying to do some more with this code. I am trying to get it to automatically sum the values in a certain column in the arrays. As well as trying to look at the last row of each array and count the specific values in a certain cell of a certain column for each arroay. I am trying to figure out how to do this manipulating your code but so far am not having any luck. Is this possible or do I need to right a whole new code?

  23. #23
    Forum Contributor
    Join Date
    09-09-2009
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    185

    Re: Formula stop when seeing a blank cell

    Foxguy,

    Is there a way that I can look at the last row of the set uf duplicates, and look in a certain column to see if the value contains a certain letter and if it does count it?

    I think that your seperate code is the way to do this but I might be wrong.

    Your help is appreciated!

  24. #24
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Formula stop when seeing a blank cell

    Sorry, I can't figure out what you're asking.

    Either post another workbook, or go back to the one I posted, and tell me what you're trying to do. What cells are you looking in and for what, and where would you want results put?

  25. #25
    Forum Contributor
    Join Date
    09-09-2009
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    185

    Re: Formula stop when seeing a blank cell

    What I am trying do is, after the each set of duplicates is set, look in a certain cell on the last row of each set of duplicates and see if that cell's value contains the letter "V" anywhere in it's value and count it. As far as where to put it, it doesn't matter to much, I just need a cell that accumulates if the conditions is true.

    Also, in that set of duplicates, there is another column, that contains prices, and I just need the formula to autosum those prices and put the total in the cell directly beneath the set of duplicates directly below the prices.

    I hope that made sense. I'm on my cellphone typing this. Also, thank you so much for your help in correcting me in my code practices. I was in such a rush to just get something done, and the Book I am reading is really stressing that good clean organization is probably the most important thing in writing code.

    Once again I can't thank you enough!

  26. #26
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Formula stop when seeing a blank cell

    Sorry, this took so long.

    I believe it would be easier to write another macro to go back in and sum the column and look for the "V"s.

    This should get you started. Be sure and give it the correct columns in the section between the '***********
    Please Login or Register  to view this content.

  27. #27
    Forum Contributor
    Join Date
    09-09-2009
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    185

    Re: Formula stop when seeing a blank cell

    Quote Originally Posted by foxguy View Post
    Sorry, this took so long.

    I believe it would be easier to write another macro to go back in and sum the column and look for the "V"s.

    This should get you started. Be sure and give it the correct columns in the section between the '***********
    Please Login or Register  to view this content.
    Hey Foxguy,

    Not a problem at all. You're helping me and I greatly apprciate it.

    Where do I put this code and how do I run it? Do I add it to the previous VBA that you wrote? I noticed that it is a function. Also, I noticed a message box in the code. I was curious if I could get the reslults to be displayed in a cell?

  28. #28
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Formula stop when seeing a blank cell

    Quote Originally Posted by whatsmyname View Post
    Hey Foxguy,

    Not a problem at all. You're helping me and I greatly apprciate it.

    Where do I put this code and how do I run it? Do I add it to the previous VBA that you wrote? I noticed that it is a function. Also, I noticed a message box in the code. I was curious if I could get the reslults to be displayed in a cell?
    Sorry about the function. Just change it

    Please Login or Register  to view this content.
    be sure and delete the "As Boolean".
    My error trapping is dependent on it being a Function. It works exactly the same as a Sub. The main difference as far as you are concerned is that it doesn't show up on the list of macros to run.

    Put it in any Standard Module, probably the same module where you have "Separate".

    I put the message box in just to show you that it works. It's up to you to put the value where you want it, (and delete the msgbox).

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1