Help Neede Creating UserForm

[bergjes]

Hi,
I'm working on a UserForm that entering / updating sales data must simplify.
My knowledge of VBA is minimal, so I try here and there some information and codes from the internet to pick and stick together.
In attached file you can see how far I am.
If there are any comments or regarding the design please let me know.

Entering new data is no problem with the form, but now I want to build something so I can update existing records or supplement.

Here is where I got stuck.
I found a piece of code that retrieves data from an existing worksheet.
I have to change the code so that the record is found based on the date and store.
The formula I think I should use to find the row number is: MATCH (K1 &K2; data_datum & data_winkel, 0)

where cell K1 and K2 must be replaced by values of the labels of the corresponding labels of the userform (DTPicker1 and ComboBox1).
This concerns an array formula.
But I have no idea how implement this in my VBA code.
Can anyone help me?
The relevant code that I think should be changed is:

Private Sub UserForm_Activate()          
Dim ActiveR As Long     
Dim aCell As Range     
Dim aRow As Long          

ActiveR = MATCH(DTPicker1.Value&ComboBox1.Value;data_datum&data_winkel;0)     
FindValue = Sheets("DataInput").Cells(ActiveR, 1).Value          
TextBox1.Value = Sheets("DataInput").Cells(ActiveR, 3).Value     
TextBox2.Value = Sheets("DataInput").Cells(ActiveR, 4).Value     
TextBox3.Value = Sheets("DataInput").Cells(ActiveR, 5).Value            
End Sub

[bergjes]

Hi,
I'm working on a UserForm that entering / updating sales data must simplify.
My knowledge of VBA is minimal, so I try here and there some information and codes from the internet to pick and stick together.
In attached file you can see how far I am.
If there are any comments or regarding the design please let me know.

Entering new data is no problem with the form, but now I want to build something so I can update existing records or supplement.

Here is where I got stuck.
I found a piece of code that retrieves data from an existing worksheet.
I have to change the code so that the record is found based on the date and store.
The formula I think I should use to find the row number is: MATCH (K1 &K2; data_datum & data_winkel, 0)

where cell K1 and K2 must be replaced by values of the labels of the corresponding labels of the userform (DTPicker1 and ComboBox1).
This concerns an array formula.
But I have no idea how implement this in my VBA code.
Can anyone help me?
The relevant code that I think should be changed is:

Private Sub UserForm_Activate()          
Dim ActiveR As Long     
Dim aCell As Range     
Dim aRow As Long          

ActiveR = MATCH(DTPicker1.Value&ComboBox1.Value;data_datum&data_winkel;0)     
FindValue = Sheets("DataInput").Cells(ActiveR, 1).Value          
TextBox1.Value = Sheets("DataInput").Cells(ActiveR, 3).Value     
TextBox2.Value = Sheets("DataInput").Cells(ActiveR, 4).Value     
TextBox3.Value = Sheets("DataInput").Cells(ActiveR, 5).Value            
End Sub

Here is what I have so far.

[bergjes]

Hi,

can someone help me with the following VBA code rewrite to
application.Match function?

The code below works well as long as something is found, otherwise I get an error message.
I understand that the error handling is easier to tackle with function application.match

This is the working code:

ActiveR = Evaluate("MATCH(""" & lDatum & Me.ComboBox1.Value & """,data_datum&data_winkel,0)+1")

ActiveR = Evaluate("MATCH(""" & lDatum & Me.ComboBox1.Value & """,data_datum&data_winkel,0)+1")

This is what I made of it, but don't work.

ActiveR = Application.Match(""" & lDatum & Me.ComboBox1.Value & """, data_datum & data_winkel, 0) + 1

Many Thanks,
Erik

[Ken Puls]

The syntax for Match is actually:

Application.WorksheetFunction.Match(Lookup_Value, Lookup_Array, Match_Type)

I'm not sure what the lDatum is, or what data_datum and data_Winkel are, but assuming you want the to look up the combobox value in a range called "data_winkel" on the "data_datum" worksheet:

ActiveR = Application.WorksheetFunction.Match(Me.ComboBox1.Value, Worksheets("data_datum").range("data_winkel"), 0) + 1)

[bergjes]

Problem SOLVED.

Thanks!