Help Neede Creating UserForm

bergjes

New member
Joined
Nov 27, 2012
Messages
4
Reaction score
0
Points
0
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:

Code:
Private Sub UserForm_Activate()          
Dim ActiveR As Long     
Dim aCell As Range     
Dim aRow As Long       [COLOR=#FF0000]   

ActiveR = MATCH(DTPicker1.Value&ComboBox1.Value;data_datum&data_winkel;0)[/COLOR]     
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
 
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:

Code:
Private Sub UserForm_Activate()          
Dim ActiveR As Long     
Dim aCell As Range     
Dim aRow As Long       [COLOR=#FF0000]   

ActiveR = MATCH(DTPicker1.Value&ComboBox1.Value;data_datum&data_winkel;0)[/COLOR]     
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.
 

Attachments

  • omzet.xlsm
    38.5 KB · Views: 28
VBA application.match

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:
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.
Code:
ActiveR = Application.Match(""" & lDatum & Me.ComboBox1.Value & """, data_datum & data_winkel, 0) + 1

Many Thanks,
Erik
 
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:

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