Help neede "Array" formula VBA

[bergjes]

Hi,

I'm trying to make a userform to display data from a worksheet previously entered.

I need two keys for finding the right data, "Datum" and "Winkel".

I have this code that almost works, but my problem is that I have to make de Array formula dynamic filled with values from ComboBox1 and ComboBox2 and
I really don't know how to do that.

here is the code that has to be changed:

Please Login or Register  to view this content.

B7 has to be the value from ComboBox1
A9 has to be the value from ComboBox2

Here is the complete code:

Please Login or Register  to view this content.

[Norie]

Perhaps something like this:

Please Login or Register  to view this content.

[bergjes]

Hi Norie,

thanks for your quick reply, but unfortunately your code does not work?

Any other suggestions?
I'm just learning

[Norie]

How does it not work?

I can think of a few possible things that could go wrong but it would nice to know.

My bet is on a type mismatch becasue the formula evaluated to an errror.

Could you attach a sample workbook?

[bergjes]

Hi Norie,

here is my file.
It is Userform2

What I am trying to build is a Userform for entering data wich is written on the worksheet DataInput.
The userform has to check if a record for the specific "Datum" and "Winkel" already exists, if so it has to pul up the existing data into the form
so it can be changed and re-written back to the worksheet.

Hope this helps.

Thanks again.

[Norie]

You aren't using Evaluate in the code in that workbook.

Anyway, can I clarify what you want to do?

You want to look in B7:G7 for a date and in A9:A10 for a winkel (shop?).

[bergjes]

Hi Norie,

sorry if I'm not making myself clear enough.

The code "Evaluate" is used in userform2 (a form for testing purpose)

All data is beeing written to the worksheet "DataInput"
Here I want to look in Column A for a Date and in Column B for a winkel (= Shop )

[Norie]

My apologies I was looking at the first worksheet and userform1, which I couldn't even get to open.

This can probably be done with a formula but it might be better to use VBA.

Would that be an option?

[bergjes]

That would be nice.

[Norie]

What values go in what textboxes?

[bergjes]

Whatever suits you well ?

I did:

DTPicker = datum
Combobox1 = RangeName = Winkels
TextBox1 = Omzet
TextBox2 = Klanten
TextBox3 = Uren

TextBox4 = Jaar = Calculated Field from DTPicker = (Year)
TextBox5 = Week = Calculated Field from DTPicker = (WeekNumber)
TextBox6 = Day = Calculated Field from DTPicker = (Day - short)

[Norie]

Aren't some of those textboxes on Userform1?

I came up with this for Userform2:

Please Login or Register  to view this content.

[bergjes]

Hi Norie,

works like a charme!!!!!!!!!
Thanks.

This worksheet is for testing purpose only.
Eventually I want 1 userform that does it all (Add - Search - Change - Delete) , so I have a long way to go.
Especially this is my first try with VBA.

Gr,
Erik