[VBA] Month Matching problem

[frankyuen1028]

Hi, I am a newbie of VBA, but I can understand the very basic codes. I have been learning through using the marco recorder.

However, I faced a problem that I cant use the macro recorder to solve it, and could anyone give me some help please.


Problem:
I would like to have a VBA code that pulls the data from a lookup worksheet onto a result sheet. It will become more clear if you could carry on reading.

Background/context:
----------------------lookup sheet---------------------------

There are 3 scenarios each of which has a month row and a number row which corresponds to a particular month. The difference among the scenarios is that the order of the month has an irregular pattern.
----------------------result sheet---------------------------
It only has a month row, and I would like the VBA code to pull the data according to the order of the month set below the month row on this worksheet. The year in the lookup sheet can be ignored, as long as the month is matched. The VBA code should be able to adapt the 3 scenarios set on the lookup sheet.

Please find attached a spreadsheet that includes the problem described above.
Thank you!

[PCI]

How do you select the scenario to use: a textbox is good ??

[PCI]

Is it what you want ?

Please Login or Register  to view this content.

[frankyuen1028]

Hi PCI,

Thank you for your reply, I think it is very similar to what I need.

The reason that I introduced scenarios is to capture the possible pattern of the data/problems. In the real case, there wouldnt be any scenarios, there would be one month row which could appear as one of the scenarios with some numbers below it on the lookup sheet, and regardless the pattern of the month (scenarios show different pattern of the month), I would like to have a code that pull the numbers from the lookup sheet on to the result sheet according to the pattern of the month shown on the result sheet.

(In the real case, the data on the lookup sheet is pulled from an input sheet which has the raw data provided by others, and the raw data always have different month format)


Please let me know if further clarification is needed.

[PCI]

Here a remake
A first macro "Scenario_Prepa" is preparing the data in sheet "Lookup"
A second one "Treat" to do the job
Comment the results

Please Login or Register  to view this content.

[frankyuen1028]

Thank you I am only using the "treat" code and I got some numbers however they dont match up:S

Could it be the format of the dates on the lookup sheet that causes the problem?

Please see the attached photos.
lookup sheet
lookup.JPG

result sheet
result.JPG

[PCI]

I have not been able to reproduce the issue: See file attached.
Can you send a sample of your file

[frankyuen1028]

Thank you, it worked, I made a silly mistake while changing cell references.

Thanks for your help, I added your reputation!

[frankyuen1028]

I have actually got another problem which is slightly more complicated than this problem.

Instead of only matching month only, I would also like to match the year and for a particular person's name.

Objective :
To pull the people's names between the "identifier 1 & 2" and also their corresponding numbers from the lookup sheet onto the result sheet. However, the people's names have to sit in between the "identifiers 3 & 4" and below "identifier 3" and their corresponding numbers have to match the date set on the result sheet.

I can use "find" which is the vba code to identify the people's name and using copy and paste vba code to paste it on the result sheet. My vba skills is not very good, the code is very long LOL

The desired result is shown on the endin mind sheet

Please find attached a spreadsheet with a new problem.

[PCI]

Try

Please Login or Register  to view this content.

[frankyuen1028]

Sorry for the late reply.

I am trying to get my head around the code you wrote.

However, if I change the year which is in 17 into 16 on the lookup sheet (from Jan 17, Feb 17, Mar 17 to Jan 16, Feb 16 and Mar 16), the code doesnt ignore it on the result table, instead it fills under Jan 17, Feb 17 and Mar 17

[frankyuen1028]

I have combined your code with mine with the new problem introduced, please see attached
Thanks a lot!

[frankyuen1028]

Btw I have tested the new code, it works fine with the previous problem but not the new problem

[PCI]

See next code
Take care about the date header, there is some text and not date! see file attached

Please Login or Register  to view this content.

[frankyuen1028]

Thank you PCI

It works perfectly fine!