excel vba-how to use macros to return the match value?

i am trying to use vba code for macros to automate for return the quantity value if the date(day/month) and title is match the calander format below.
Example of the table for return value.
Capture1.JPG

below is the calander format for get the match value from the table.

The output will be like
Capture2.JPG




thanks.

Not sure why you think you need a macro for this, when a formula will probably also do what you want.

Either way, I suggest that you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

Yes, you can attach a file here.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Cross posted here https://www.mrexcel.com/forum/excel-...tch-value.html

My spin using Sheet2 as a template...result sheet3

Please Login or Register  to view this content.

sry for another question,if i added one more month (july'18) into the following M column, so how do i modify the code make it can define and base on the month to return the value in collect month template?

if i add other column like"otherthing"will not effect the function right?
if i add more rows data will also not effect the function right?

*do you have any idea to automate generate monthly template?because every month day are not same.

Thanks!

That my friend is a whole new can of worms...
I feel that if the current code gets adapted to accommodate July then that is not going to work as you will then require another month and so on and so on...
What are you actually wanting to achieve...

sry,i am new to the excel vba,i have attached the file,now i want output at least 2 month of template such as June and July, and have added June and July data inside the table already.

i think the automate template can done myself, but idk how to do the 2 monthly template base on the date.(all in the attached file)

and thank you a lots!

now i want output at least 2 month

Please be advised that we do this as enjoyment to help others...We do not get paid...and do not like demands...

I am busy working on a Calendar template and will supply solution shortly...

This code works with a Calendar template...
Checks date, then generates calendar, copies over template each time...

Please Login or Register  to view this content.

Works perfectly with my file I uploaded...
Open file and press the Excel VBA image

main-qimg-3bce35be3f3917b1579a9e38f98279e8.png

Cannot recreate...like i said, Works perfectly with my file I uploaded...
Herewith result after code ran...
cHECK IF ANY OF YOUR VALUES HAVE CHANGES AFTER OPENING FILE....
https://excelmacromastery.com/vba-type-mismatch/

@sintek, I tried you file and got the same error.

Please Login or Register  to view this content.

Extra information

Please Login or Register  to view this content.

I don't think that cells is completely empty. It thinks it is text which is why you get the Error 13.
If you select the cell and press delete then it will work.

this should fix that problem

Please Login or Register  to view this content.

Hi Andy

So glad someone can recreate and offer assistance...
Strange I don't error...What cell was the culprit?

Yeah, I tested the file and I got the same error. Andy is quite right in his diagnosis.

BTW you can try tot = Val(Cells(sRow, iii).Value) also to see if that works as per your calculation.

Please elaborate...where must code be corrected...show mistake .

Okay check this file and see if that works for you.

Hey guys...
Seeing as I am unable to recreate all the errors, can someone please tell me where the issues are...So I too can learn.

@ sktneer...Your code adaptation creates incorrect calendar dates...All templates are the same...

but the sum(calculate) have problem such as wrong calculating.

@ Step01

Can you please provide information on the errors...

Oh yes. Didn't notice that. Actually didn't go in that much of depth.
Maybe OP can point out the errors he is getting and then you can tweak it accordingly.
But it's strange that you didn't get any error during the code execution.

One thing you can do is, you can change the formulas in the calendar on Sheet2 to place a zero instead of empty string and custom format the formula cells with 0;-0;;@ to hide the zeros returned by the formulas.
e.g. in C4

Please Login or Register  to view this content.

and in D4

Please Login or Register  to view this content.

and so on.

That way you can handle the error with the line tot = Cells(sRow, iii).Value

Other issue seems to be the missing dots as you have used the With and End With blocks. If you can fix them, maybe you will get the correct sum on Sheet3.
Did you notice that during the copying the calendar and pasting it on Sheet3, Sheet3 gets activated and that may be one of the reason for the incorrect sum on Sheet3.

Can you please provide information on the errors...

Don't get upset.

hi all,
i discover that the return have another error such as return the are not match the table example is attached img.
another is the calendar template are all same cause the day error.

example error1.1.JPG and example error1.2.JPG


thanks.

@ sktneer...Never upset lol...was merely for dramatics...
Yes lotsa missing dots ...My bad..Have corrected.

@Step01
Do me a favour...Look at result in Post 19...
Do you agree that the result on Sheet2 is accurate?
Both the calendar setups & totals are correct...

Why do I not get any errors when I run the code...can anyone clarify this...

result.png

What file are you using...Have you done what I have so nicely asked...Have you checked post 19 and the resultant image in post 33

Well unfortunately I cannot create your errors and you have agreed that the result in post 33 is correct.
From my side, I am unable to assist you any further...Good luck

I found some time to tweak it. See if you get the correct desired output now. And then change the sheet reference in the code as per your new file and see if this works in that case also.

@Sktneer

Exactly the same result I got...Awesome
Still would love to know why it worked for me
Thanks for sticking by to assist OP with this...

@Sintek

Well, honestly I am not sure why this worked for you without raising an error. Maybe something related to the version you are using, but not sure.
But when you use nested With blocks you need to be very careful and as I said earlier, you need to qualify all the ranges/cells references with a dot which were clearly missing in the codes and hence producing the wrong outputs.

You're welcome Step01! I hope that will work fine with your original workbook as well after changing the sheet reference.

@ sktneer
Thanks for feedback...Will definitely check my dots within nested with blocks in future...
Yeah, probably version... Windows 7 64bit Office 2007

@ Step01
Why don't you upload the file you are making use of...

@sintek @sktneer

hi sir,

sry, i am tried few time it seen like no problem, but the output like some will duplicate or error value return.it it this file i attached is calculate the rate? because i just like to calculate the quantity.


Thanks!!!

Here you go...Missing name manager for Calendar setup...row criteria incorrect..
This tests fine ...see output

You have duplicate dates in columns and therefore it renders duplicate calendar events...

EDIT... Your new file uploaded above in post 46 tests fine for me...

Wow, finally...Congratulations...Tx for rep +

i am sry my friends,i used this template few time but some time meet some error,like some of the category have quantity but not display the sum, some quantity are sum wrong , and the calender template come out with wrong date such as Feb did't have 31 but sill display until 31, did u have Suggestion for this?

Thanks!

attacked is the file that i am facing error.

I can only assume that you made an error when adapting...Sorry Step01...Just don't have any free time to have a look...Busy with a big project...
I suggest going back to the Post which housed the correct working file and go through it step by step...