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 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
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")Code:ActiveR = Evaluate("MATCH(""" & lDatum & Me.ComboBox1.Value & """,data_datum&data_winkel,0)+1")
This is what I made of it, but don't work.
Many Thanks,Code:ActiveR = Application.Match(""" & lDatum & Me.ComboBox1.Value & """, data_datum & data_winkel, 0) + 1
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)
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
Problem SOLVED.
Thanks!
Bookmarks