Posts by JCabral

Pike, thanks, work perfect!

Is it possible for someone to clarify for me whether it is feasible to do what I am asking?

Hi

As everyone knows, the colors of the USERFORM's come in the format "&HxxBBGGRR&", ie:

Start indicator - &

H of Hexadecimal - H

xx - Constant Color - 00 or 80

Blue RGB - BB

Green RGB - GG

Red RGB - RR

End indicator - &

What I needed was, with Regular Expression - RegEx, extract from that string the substring "BBGGRR", I know it is possible to do it with the normal text functions and I know how to do it that way, but I needed it to be with RegEx.

Basically what's failing me is defining the pattern to extract the substring.

Thank you in advance for all the help.

Jorge Cabral

NOTE: I have attached a file that checks if the String format is correct, I still need to extract the Sub_String with RegEx

Carim

As I told you, I don't give up!

It is not because I asked for help here, or in any other forum, that I do not continue to try to solve the problem.

I often have a solution to the problem, for which I ask for help.

I ask for help just to see if there is a better solution or a faster solution.

I have already managed to solve this problem in VBA, and I am trying to improve the code I have.

Thank you for your contribution.

Jorge

Quote from royUK

I've added code to sort the IN_OUT sheet to show the latest record first.

I can't really understand what you are doing, there seems way too many operations in your userform than necessary.

Hi Roy where is the code added?

This solution solves one problem for you, but adds another one, that is, the date problem I think is solved, but by this method the column headers disappear, it is an EXCEL / VBA fault.

The solution is to put a label, for the headers, otherwise the solution is just as RoyUK says.

OK thanks.

I will be waiting for the update of your example.

I apologize for meddling in this topic.

Thanks

Jorge

Quote from royUK

I've posted several example of UserForms that do this.

I can't help with an image, attach an example workbook containing some data and your UserForm.

My web site also has examples.

Hi royUK

I think the link to the Advanced DataBaseForm on your website is broken

Thanks

Jorge

Hi Carim

Bearing in mind that we are already going, in the exchange of messages # 37, I tell you that I thought you had already realized that the UDF does not work in the way you have presented, taking into account what I need.

I know that what I am asking for is not easy, I will not give up but, it seems that you have already decided to give up ....

However, I have to thank you very much for your contribution during this long exchange of messages.

Thank you very much

Jorge

Hi Carim

Any particular reason?

Is there any clarification that I need to do?

Thanks

Jorge

Hi Carim

Sorry I don't understand what's missing, in my initial example what I had was a tab (DATE) with the DATA for each task (Start Date and End Date).

I put an OUTPUT tab to show which results I calculated manually, and so that whoever presented a solution could compare it with their own solution.

If it is the way i want you to present the results that is failing in my real-life workbook, I added a tab where I show how the results can be presented, after studying the code I forward the results to the "PLANEAMENTO" tab myself.

Thank you so much again

Jorge

Quote from Carim

Since you do want to specify at least the cells addresses ...

Unfortunately ... cannot help you any further ...

Hi Carim

I'm sorry but which cell addresses do I need to specify?

I thought I had clarified everything.

I hope you continue to help in this challenge, which I know is not easy.

Thanks

Jorge

Rank 1 has Start Date = ..... and End Date = .....

Rank 2 has Start Date = ..... and End Date = .....

Rank 3 has Start Date = ..... and End Date = .....

These are dates that have to be calculated.

After all periods have been calculated and ranked, these dates are the first three.

In the example I presented in the OUTPUT tab, it corresponds to the first three rows of columns J, K, L, M and N.

In other words, after applying the "Weight" to each task, I rank the highest to the lowest and are the first three.

I hope I have been clear

Yes, you are right, but I don't need them in my main layout, it is possible to create a new tab (SETTINGS) and put that data there.

Especially because I will need them, to represent them, graphically, on lines 11, 12 and 13.

Thanks again

Jorge

Hi Carim

I'm sorry, but I didn't understand what you mean by "To move forward ... The Periods, corresponding to 1, 2 and 3 rank ... must be Explicit ... !!!"

After calculating the various periods and ranking them according to the criteria I presented, it is necessary to check if each task is within the 1 or 2 or 3 rank and when placing in the N, O or P column a value other than zero, an "X" is shown or not indicating whether the task is within the period, notice that a task may be within the three ranks.

Thank you so much again

Hi Carim

Let me clarify the following:

In columns N, O and P are the results of the tasks being, or not, in the first three rank.

The Periods, corresponding to 1, 2 and 3 rank will be represented graphically in lines 11, 12 and 13, by conditional formatting or by shapes I have not yet decided.

Hi Carim

First of all I apologize for my English, I may not be completely clear on what I want.

But it is like that, for each task I only have a start date and an end date for the task. There are no more data.

Everything in the "OUTPUT" tab was manually calculated to show what I wanted, nothing of what is there is data, it is just the results of calculations.

In tasks I have start and end dates for tasks, and what happens is that some of these dates overlap and it is these periods that we want to know.

I hope I have been clearer now.

Thank you so much again

Hi Carim

Since is not possible edit my last post, here is an update.

I was seeing your solution better (initial file - TestPMC_Original-V3.xlsm), and I notice that it is not calculating the periods with the highest concentration of tasks, but only the number of tasks.

Note that the available data is only the start and end date of the tasks. What was in the OUTPUT tab was all calculated manually.

The data are in the DATA tab, columns A, B and C, what I want is the calculations that are in OUTPUT tab, columns A to F.

Hi again Carim

This is the layout of my real-life workbook, I just changed the names of the tasks. As you can see, this is a Gantt layout in which I have several tasks and the Start (Column C) and End (Column E) Dates for each task.

The data in this layout is not fixed, I repeat is not fixed and depends on the Department (Column L), that is, when I choose the department all values from Column A to Column K change., None of this is happening now, because I simplified this file as much as possible.

The main columns for the case in question - task overlap periods - are columns N, O and P.

Thus, 1, 2, 3 means:

1 - first period with the highest concentration of tasks

2 - second period of greatest concentration of tasks;

3 - third period of greatest concentration of tasks.

If task1 is in period 1 then column N shows a "1" if neither period 2 nor 3 appear in columns O and P then "0" appears - this has to do with conditional formatting.

It is enough that one day of the task1 duration period, falls within any period 1, 2 or 3 to appear "1" in column N, O or P, that is, it is not necessary that the start and end date of a task is all contained in either period 1,2 or 3, one day is enough.

This was what I needed .... not easy, I know!

The cherry on top of the cake, was if it were possible, instead of having "1" and "0" in columns N, O and P, it was having the number of days for the duration of each task, which are in each period 1 , 2 and 3.

I don't know if this explanation of mine is understandable - "compreensível", if you have any doubts tell me.

Thank you so much again.

Jorge

Hi Carim

I will certainly have many difficulties, as you can see under my Avatar I am a Beginner, the most certain thing is to drop this topic in my project.

Thank you so much again.

Jorge