Results 1 to 10 of 10
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Why not work vlooup function (2000 sr 1)

    Private Sub CommandButton1_Click()

    Call ORDINA

    RIGA = ScrollBar1.Value

    UserForm1.TextBox1.Value = Sheets("RATE").Cells(RIGA, "A").Value
    UserForm1.TextBox2.Value = Sheets("RATE").Cells(RIGA, "K").Value
    UserForm1.TextBox3.Value = Sheets("RATE").Cells(RIGA, "J").Value
    UserForm1.TextBox4.Value = Sheets("RATE").Cells(RIGA, "M").Value
    UserForm1.TextBox5.Value = Sheets("RATE").Cells(RIGA, "AD").Value
    UserForm1.TextBox7.Value = Sheets("RATE").Cells(RIGA, "D").Value
    UserForm1.TextBox8.Value = Sheets("RATE").Cells(RIGA, "E").Value
    UserForm1.TextBox10.Value = Sheets("RATE").Cells(RIGA, "I").Value
    UserForm1.TextBox11.Value = Sheets("RATE").Cells(RIGA, "L").Value
    UserForm1.TextBox12.Value = Sheets("RATE").Cells(RIGA, "AB").Value
    If Sheets("RATE").Cells(RIGA, "AI").Value = "U" Or Sheets("RATE").Cells(RIGA, "AI").Value = "M" Then
    USER = Sheets("RATE").Cells(RIGA, "AI").Value
    UserForm1.TextBox13.Value = Application.WorksheetFunction.VLookup(USER, Worksheets("TABELLA").Range(Worksheets("TABELLA"). Range("O1"), Worksheets("TABELLA").Range("P3").End(xlUp)), 2, False)
    End If

    UserForm1.TextBox13.Value = Sheets("RATE").Cells(RIGA, "AI").Value

    UserForm1.TextBox14.Value = Sheets("RATE").Cells(RIGA, "G").Value
    TextBox6 = Format((Sheets("RATE").Cells(RIGA, 6).Value), "##,##0.00")

    End Sub

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Why not work vlooup function (2000 sr 1)

    Sal

    Change you vlookup to

    UserForm1.TextBox13.Value = Application.WorksheetFunction.VLookup(USER, Worksheets("TABELLA").Range(Worksheets("TABELLA"). Range("O1:O3"), Worksheets("TABELLA").Range("P3").End(xlUp)), 2, False)

    It appears that you had not set the range correctly but this seems to work...is this what you wanted?
    Jerry

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Why not work vlooup function (2000 sr 1)

    Use

    UserForm1.TextBox13.Value = Application.WorksheetFunction.VLookup(USER, Worksheets("TABELLA").Range(Worksheets("TABELLA"). Range("O1"), Worksheets("TABELLA").Range("P3")), 2, False)

  4. #4
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why not work vlooup function (2000 sr 1)

    tks, good!

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why not work vlooup function (2000 sr 1)

    Hi Hans attached is a new version with modify....
    I would want to write the result of entry from userform into mdb after i have pressed botton MODIFICA...
    I have maked a macro AGGIORNA_MASTER, but result is not good, naturally ....
    The matching to intercept the record in mab are in filed 29 and in column AC of sheet
    I hope( as usual) to understand me.....;-)

  6. #6
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why not work vlooup function (2000 sr 1)

    dont worry solved....
    in refrence to "Update record..." on this post.
    ...

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why not work vlooup function (2000 sr 1)

    Long piece of code moved to attachment by HansV

    aaaaccccccccccccc....
    But this not form me!
    I for error i insert a wrong value how i come back?
    In textbox4 if i cancel the value entered for error clear all cell into range L, M AB, AD, and AI and clear value into rs

    .Fields("PROVA12") = B
    .Fields("PROVA13") = C
    .Fields("PROVA28") = D
    .Fields("PROVA30") = E
    .Fields("PROVA31") = F

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Why not work vlooup function (2000 sr 1)

    What do you mean by "In textbox4 if i cancel the value entered"? That you clear TextBox4?

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why not work vlooup function (2000 sr 1)

    If you see this is the unique textbox free for entry a value...
    After i have inser a value i can modify all cella and all recoordset with MODIFICA button.
    My prob is for error i insert a wroong value into textbox4 i would want te clear all, cells and recordsets for the new entry value into textxbox4...

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Why not work vlooup function (2000 sr 1)

    Just don't insert a wrong value then! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    If you do enter a wrong value, why don't you just correct it and click "MODIFICA" again?

Posting Permissions

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