Add like text totals of columns at bottom of column

[duugg]

Hello,

Note that the "Fruit" and "Fruit Totals" column letter position will always be different every time I run the macro, so I would like the macro to find these columns by name rather than by column letter.

I'd like a macro that adds the total number of fruits in the "Fruit" column by looking doing 2 things...

1. Add the total number of "Fruits" in the "Fruit" column, find the bottom-most row of data in the "Fruit" column, go down 2 rows, and create the text named "Fruits Total"

2. In the cell directly to the right of "Fruits Total" put the value of the total number of fruits in this cell (which will be in the "Fruit Totals" column)

3. Add the total number of "Apples" in the "Fruit" column, find the bottom-most row of data in the "Fruit" column, go down 2 rows, and create the text named "Apples Total"

4. In the cell directly to the right of "Apples Total" put the value of the total number of apples in this cell (which will also be in the "Fruit Totals" column)

5. Repeat steps 3 & 4 for the other fruits


Best to show you an example

Please Login or Register  to view this content.

Thanks much!

[StephenR]

Will the fruits vary or are they the same as in your example every time?

[Krishnakumar]

Hi,

Select the range and run the VBA.

Please Login or Register  to view this content.

HTH

[StephenR]

Another approach. Don't understand what your Fruit Totals column has to do with anything.

Please Login or Register  to view this content.

[duugg]

Sorry Stephen,

I was away for awhile,

The "fruits" are just "dummy" text. I just want to "ADD" the number of times a certain word appears in a cell in a certain column.

I will try both codes out

thanks to both

[duugg]

Krishnakumar,

After looking at your code, I'm not even sure what to do with it, sorry.



Stephen,

I tried running your code and received this error...

Run-time error 91

Object Variable or With block variable not set

on this line

Please Login or Register  to view this content.

any thoughts?

thanks

[Krishnakumar]

Hi,

Krishnakumar,

After looking at your code, I'm not even sure what to do with it, sorry.

See the attachment.

[duugg]

Hmm, interesting, is there a way to have this automated without having to manually select the range?

Thanks much!

[StephenR]

I should have mentioned that I assumed Fruit would be in the first row. Perhaps that is not the case, in which case try this:

Please Login or Register  to view this content.

[duugg]

Hi Stephen,

Yeah, I got the same error unfortunately.

Please Login or Register  to view this content.

This seems like a tough one.

[StephenR]

In that case please attach a sample of your data AS IS.

[duugg]

Stephen,

Here's the workbook you requested.

Thanks,

duugg

[StephenR]

duugg - in your original post the heading was "Fruit" but in your attachment was "Fruits" so wasn't picked up. We can't find part of the cell because you have another cell with "Fruits" in it. Btw you also have the word Fruits in your list!

Please Login or Register  to view this content.

[duugg]

Stephen,

Thanks, you got some reputation from me!

Okay, we are very close now

I noticed that the total count type was off by 1, so I looked at the code and took out the part here in red...

Please Login or Register  to view this content.

Also, I changed this line of code here...

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

...because it wasn't working initially. I couldn't figure out why at first but then I realized that I had changed the worksheet name. So I managed to figure it out all on my own! I am learning this stuff and it's pretty cool stuff.

After that, it worked great...EXCEPT when there were blank cells. If there's a blank in there, which there will definitely be blanks, I get an error.

2 Things...

Number 1 - Can we modify this code to count the number of blank cells as well? (This will eliminate the bug as well)

Number 2 - Can we modify the code to color all blank cells red?

I think that will do it!

thanks much

duugg

[StephenR]

Have changed approach as Advanced Filter doesn't appear to like blanks:

Please Login or Register  to view this content.

[duugg]

Stephen,

This is awesome!

Geez, I hate when, after I see something work exactly as intended, I realize a problem that I hadn't realized before. Here's the problem...I plan on using this awesome code for several columns (see question 3 below) and some columns are right next to each other, so, using the macro as is would cause data to overlap
But this problem made me realize that I would rather have the data on a new worksheet anyways (question 1, below).


How hard would it be to modify this "awesome" code to do this...

1. Instead of putting all this info underneath the bottom-most row of data, create a new worksheet named "Totals" and put all this data starting at the first blank cell in column a instead

2. Put the focus back to the worksheet that the macro started on

3. Do I have to use this whole block of code for each column or can I add columns to the existing code?

Something like this in red below?

Please Login or Register  to view this content.

4. Each new column I run the macro on, it would also go to the same newly created worksheet named "Totals" and would be below the bottom-most row of data.

So let's say I ran the first macro named "Fruits".
All totals data would go to the new worksheet named "Totals".
Fruit names would be in column A
Fruit totals would be in column B

And let's say after running the "Fruits" macro, it created 10 rows of text in columns A and B, this would make row 11 the first blank row.

Then, I run same macro, but modifying it for the "Vegetables" column. After running the "Vegetables" macro, all data from this macro would also go to the new "totals" worksheet, which would put the data starting at the first blank row in column a, in this case, row 11.

Whew, I really believe I'll be done after this because ALL data from all my columns will be in one centralized place.

Thank you sooooooooo much for helping me with this Stephen! I'm creating my own fireworks with this macro today!



Thanks again!

duugg

[StephenR]

This code loops through an array of search items which you can adjust.

Please Login or Register  to view this content.

[duugg]

Hi Stephen,

Thanks for the reply

I'm getting this error message:

Run-Time Error 91:

Object Variable or With Block variable not set

After pressing debug, this line of code was highlighted yellow

Please Login or Register  to view this content.

It does create a "Totals" worksheet even after the error but doesn't bring the focus back to the worksheet the macro started on.


thanks,

duugg

[StephenR]

If you remember, that's probably because the value was not found on the sheet so you can add the middle line below to exit the code rather than erroring.

Please Login or Register  to view this content.

[duugg]

Hi Stephen,

Wow, this code stuff requires some patience...whew

1 self-fix and 1 "duugg oversight"


The "duugg oversight" - All columns that I'll be counting text on (ie Fruits, Vegetables, etc), obviously need to "stop counting" at some point. Currently, the macro stops once it gets to the first blank cell in its respective column.


MY Fault for not telling you this...but I need the macro to change it's stopping point from the first blank cell in each column to instead stop counting once it is equal to the bottom-most row cell with data in the "Cust Num" column...




Using this as an example...

Please Login or Register  to view this content.

Therefore, the text count would be this...

Please Login or Register  to view this content.

The self-fix

I fixed the "worksheet focus" problem by removing this line of code...

Please Login or Register  to view this content.

and adding the 2 lines of code below

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

As always, thanks much!

[StephenR]

Currently, the macro stops once it gets to the first blank cell in its respective column.

No, it goes to the last used cell in the Fruits/Veg column.

I need the macro to change it's stopping point from the first blank cell in each column to instead stop counting once it is equal to the bottom-most row cell with data in the "Cust Num" column...

So if there are fruits below that row ignore them?

duugg- would you like to leave it for 24 hours and think long and hard about what you need, because I can't keep changing this code indefinitely?!

[duugg]

Hi Stephen,


I can say for sure that there will NEVER be a text count in any column that's lower than the bottom-most row of data in the customer column.


It's funny you say that about thinking about the code for 24 hours..because I noticed that in the fruits column for example, BANANA and banana and Banana were counted seperately (in 3 rows on the totals worksheet).

I was going to try putting in some code BEFORE this macro was run that changes all text in the criteria columns (Fruits, Vegetables, etc) to UPPERCASE (except header row) but, if the code for counting BANANA, banana & Banana as the same text in a column is fairly simple, then, putting that in would be fantastic.

So, at a minimum, if you could at least change the count thing to match the "customer number" column, that would be great.

If the code to count all cases of text for the same word as one is fairly simple, that would FINISH THIS. Otherwise, I will convert all relevant columns to uppercase before this macro.

WHATEVER YOU PROVIDE IN THE NEXT REPLY, I WILL REPLY TO MARK THIS THREAD AS CLOSED. (unless I get an error of course, but I will first investigate and try to fix myself).

I don't need 24 hours, promise that really is it Stephen.



P.S. On the positive side, I truly believe that my questions along with your expertise/replies in the several posts to this thread will really help a lot of people. It has already received a lot of views. Seeing the progression of a good macro like this gives novices like me and others a great perspective on the development of a good macro. Of course, this is something you may no longer understand as you are now a PRO lol.

My sincere and infinite thanks for this "gold macro"

[StephenR]

I can say for sure that there will NEVER be a text count in any column that's lower than the bottom-most row of data in the customer column.

In that case, I can't see what needs changing.

You can add the second line below to ignore case, but you'll have to check spelling (banana has two 'n's):

Please Login or Register  to view this content.

[duugg]

Hi Stephen,

The case problem was fixed, thank you for that.

FYI - My last problem only happened if there were blank cells in these 2 columns ("Fruits" or "Vegetables")

For example, if the last text entry in the "Fruits" column was "Cherry" on row 10, but I had customer numbers in the "Customer Number" column going down to row 15, then 5 "Fruit" blanks wouldn't be reported in the "totals" worksheet.

I tried this code, to highlight all cells in my vegetable column so that I can "find and replace" all blanks with "BLANK CELL", but it didn't work...

Please Login or Register  to view this content.

But, as promised, I will closed the thread.

Thanks again

[StephenR]

duugg - I stand corrected and now see what you mean. Have amended the code to, I hope, do what you want.

Please Login or Register  to view this content.

[duugg]

Stephen,


AHHHHHHHHHHHHHHHH !!!!!!!!!!!

What else can I say but............

ABSOLUTE PERFECTION!

Now I can enjoy the rest of my day without trying to figure out a workaround...

I'm sure this thread will help others as well

Many Many Thanks!