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

#### Chirag R Raval

##### Member
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.

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

#### vletm

##### Excel Ninja
Chirag R Raval
Rebus ?
With Your given information...
test this:
Code:
``````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``````

#### Chirag R Raval

##### Member
Dear Sir @vletm

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

Regards,

Chirag Raval

#### vletm

##### Excel Ninja
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?

#### Chirag R Raval

##### Member
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.

Regards,
Chirag Raval

#### vletm

##### Excel Ninja
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?

#### Chirag R Raval

##### Member
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).

Hope there are way.

Regards,

Cirag Raval

#### vletm

##### Excel Ninja
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.

#### Attachments

• 32.5 KB Views: 2

#### Chirag R Raval

##### Member
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

#### vletm

##### Excel Ninja
Chirag R Raval
If those would show something else then
what is needed value somewhere then use that my file?

#### Chirag R Raval

##### Member
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

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.

Regards,

Chirag Raval

#### Attachments

• 583.3 KB Views: 1

#### vletm

##### Excel Ninja
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.

#### Attachments

• 641.2 KB Views: 2

#### Chirag R Raval

##### Member
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

#### Chirag R Raval

##### Member
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

#### vletm

##### Excel Ninja
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.

#### Chirag R Raval

##### Member
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.

Regards,

Chirag Raval

#### vletm

##### Excel Ninja
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.

#### Chirag R Raval

##### Member
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

#### vletm

##### Excel Ninja
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.

#### Chirag R Raval

##### Member
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"

(2) EXPAND WHOLE RANGE BY CLICK ON BUTTON 3

Please guide..what can we do there?

Regards,

Chirag Raval

#### vletm

##### Excel Ninja
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?

#### Chirag R Raval

##### Member
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