Results 1 to 5 of 5

Thread: Help Neede Creating UserForm

  1. #1

    Help Neede Creating UserForm



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Quote Originally Posted by bergjes View Post
    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

    Here is what I have so far.
    Attached Files Attached Files

  3. #3

    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

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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 (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    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.

  5. #5
    Problem SOLVED.

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •