Want Macro for Daybook

Please Login or Register  to view this content.

First of all Thanks for your Solution ..

Please find the file I have attached.

Below are the modifications..

1. Please use table references instead of normal references (Because my data goes very long day by day)

2. I forgot to tell you about value " BETA " exclusion from the results population.

In formula case, I wrote like this (Master[DATE]=$B$1)*(Master[PARTY]<>"BETA")
I think lines to be modified are

Formula:

Please Login or Register  to view this content.

3. I have tried to make the same look like Formula sheet by changing font size to 14, but after running macro font size changing to normal.

please fix these problems ..

Thank you so much legend ..

waiting for the reply,

can anyone fix the above problems ..?

thank you

This remove the Range limitation: you don't need TABLEs

Please Login or Register  to view this content.

The code needs to be re-written to accommodate the exclusion of "BETA".

It does not search the entire column: only between SREC and SREC+NREC-1 (first /last records for a given date).

I have never used VBA with tables (old and old fashioned .. me, that is!) so not sure how they work.

That's ok brother.,

please fix the remaining too ..

Thanks in advance ..

Please Login or Register  to view this content.

I removed "Formula" sheet (too slow!!)

Dear Mike,

Thanks for code too (Added Reputation),

It's also working very good, and I'm at my home, so I can't tell you anything more about this right now.

because I have to test this on the original file in office,

any way thanks, and let you know within 3 hours from now ..

and dear mike,

meanwhile, try to exclude the results of " BETA " value from PARTY column ..

Thank you ..

dear mike, i'm getting the below error in my original file ..

error.JPG

You will need to post file as it is possibly a data error: check the date field in B1 as this is the parameter in the FROM statement

... or Sheet name ("MASTER")

I have tested Mike's code and it seems pretty robust to me: if data is invalid it exits the subroutine so as far as i understand it the only other parameter is the sheet name..

what I have to do now ..?

have to delete sheet " DAYBOOK " (with formulas) ..?

There is no requirement to delete any sheets: The code simply references (uses) the sheets "MASTER" and "DAYBOOK MACRO".

Can you post the file (or subset of) giving the error ?

do you mean file uploading here ..?

or are you asking to upload the file here ..?

Here's my original file,

please do the needful

thank you brothers ..

sorry sister, I forgotten (Added reputation to you too).

Onething I forgotten to tell, that is

Please exclude " PURCHASES " valued results from TYPE column.
(Previously it is " BETA " value from PARTY column, now don't exclude this)

thanks ..

ERROR GETTING ON THIS LINE

Capture.JPG

2.JPG

Thanks in advance ..

In my macro .....

Please Login or Register  to view this content.

replaces test for BETA

Originally Posted by JohnTopley

In my macro .....

Please Login or Register  to view this content.

replaces test for BETA

yes, your modified line is working to exclude PURCHASES correctly..

and Please fix the error in mike code .. thank you ..

Put this in Mike's "GetRange" function

Please Login or Register  to view this content.

to replace

If oListObject.Name = ListName Then

Please Login or Register  to view this content.

it is the total code of mike in my workbook ..

Worked OK for me and I tested on several dates.

The change only checked the values in UPPER CASE to ensure a match as previously one value was lower case, the other upper and they did not match so the GETRANGE was not created.

now what I have to do ..?

You can leave #ref error, I can solve it ..

Why because it is, I deleted some unnecessary sheets ..


No problem with your code, its working good.

https://drive.google.com/file/d/0Bzs...w?usp=drivesdk


It is my file , make Mike's code to be worked in it ..

Thanks ..

Both of your codes working like rocket.. (When deleted daybook sheet with array formulas)

only onething to be sorted per code,

John, i'm getting the below error when there are no data for a given date (but I don't like this, it should keep silent [ like in mike's code] )

Runtime error '13':
Type mismatch

And Dear Mike, excel is alerting me about the CREDIT column format ..

the number in this cell is formatted as text or preceded by an apostrophe (In John's code, there are no format alerts)

Please do the needful ..

Thanks to both of you .. (Added Reputaions )

Mike,

morning your file was worked in my home laptop.

But now in office system, it's not working

(I did not changed anything in your code yet not coming any error, just not populating results)..

Updated code ....

Please Login or Register  to view this content.

John,

Ok I understood that you have given another exit to sub. and it's working.

But at the moment of that middle sub exit, the master sheet is being in active. (But I want B2 in DAYBOOK MACRO sheet)

thanks ..

And I forgot that is please simplify the code as much as possible ..

and one morething is,

do you know why mike's code is not working in office system ..?

I am now finished with this thread:

(a) the code is as simple as I can make it : what is complex about it as it is a simple FOR /NEXT loop using in-core arrays which makes it very fast and efficient.

(b) how will I know why Mike's code is not working in a system I have no access to

(c) what does the mean ..." But I want B2 in DAYBOOK MACRO sheet)" .

I agree with your (a) & (b) points.

coming to (c),

I want the B2 cell in " DAYBOOK MACRO " sheet to be active after sub exit. (in case of middle error)

have you got it ..?

Why ?: Cell B2 is the "Party" heading and if macro exits it remains in the "Daybook Macro" tab. If you really require it to select B2 you can add the code.

As I said earlier "Je suis fini"

Originally Posted by JohnTopley

Why ?: Cell B2 is the "Party" heading and if macro exits it remains in the "Daybook Macro" tab. If you really require it to select B2 you can add the code.

As I said earlier "Je suis fini"

Sorry it's not B2, it's B1 (that is date cell)

If it too much effort to move the cursor to B1 from B2 ..........!!!!

Originally Posted by JohnTopley

If it too much effort to move the cursor to B1 from B2 ..........!!!!

Brother, if you don't mind, I will tell you onething ..

You are not getting me,

that is at the point of middle sub exit the master sheet is getting activated.

but I want the B1 in daybook sheet to be active/selected ..

when we are searching day by day transactions in daybook sheet,

if any error occurred master sheet is getting activated.

so we are disturbing from searching our transactions ..

(I think by language problem you are not getting me please understand )

I really don't understand what the issue is: the added test for a non-existent date in the MASTER data in my macro just exits the routine - no more.

If I put a date with no data in "Daybook" (formula sheet) it simply leaves a blank set of results as I would expect.

The macros, either Mike's or mine, affect this ("DAYBOOK") tab; neither macro references it.

So I don't know what errors you are getting BUT I cannot reproduce them.

Just wait 5 minutes, I will send you a video link ..

I still guessing but ...

in my macro

Please Login or Register  to view this content.

in Mike's macro before "rs.close"

Please Login or Register  to view this content.

Given the VBA code aleady present (you wrote it ?) it should have been easy to make these amendments.

Moving forward/backward between dates works OK for me (I assume this is the issue): If you have problems then .....???

brother,

Ok. Now not going to master if error comes ..

(staying in daybook b2)

thank you ..

but please find the attachment now..

and search for 15-02-2017 (today) transactions.

actually there are 27 transactions but macro result is showing only 26 (leaving last row)

I read your code 4 times and did not find any clause for that ..

please see why it is occuring and fix ..

thanks for great patience (added reputations)

The reason is the first 15/02/2017 record is record 4416 BUT record 4418 has a date of 11/02/2017 and as was explained to you many posts ago the macro expects the data to be in ascending date order. If this not so you will get the wrong results.

The reason for having the records in date sequence is so that we don't have match EVERY record to see if it is the chosen date: 4500 to check???,

The macro finds the first record with the selected date and then counts the number of records with that date to get the range to select. So I check in this case, 29 records starting at 4416 so finish at 4416+29 = 4444 (inclusive) whereas the actual last record is 4445. NOTE also the macro does NOT check the date: it "assumes" all records selected are for the correct date.

"Odd" records in the midst of that sequence will result in the "errors" you see.

You need to much more analytical in investigating why things do not work.

It is case of RIBO: "Rubbish In, Rubbish Out"

The integrity of the data is the issue NOT the logic of the macro.

@Mike,
OP already has a working solution so I don't know why he persists in getting you to look at your solution.

He has cross-posted this thread and had a third respondent reply with VBA code so 3 people have spent (some uneccessary) time on this problem

Dear Mike,

Brother Johntopley's solution is already working for me.

Please note that there is no problem with me when I receive your solution or not.

But I want to know why your code is not working in my office PC (That's why only I'm asking to fix).

Now it is your choice to fix this.

Really thank you so much for the time you spend to help me ..

That's OK

Thread marked [ SOLVED]

Thank You