adjust TextBox Value's Code

[Hany ali]

hello every one
I Want your help to adjust this code for TextBox1 's Value ,to change when i choose from ComboBox1 & ComboBox2 ,because i want the last price for this choosen client with any item from date in Table from C2:F
THE PRICE WILL BE IN Column F
pls i want the same Result as this Photo

 

[Hany ali]

pls Help me

 

[Kenneth Hobson]

Welcome to the forum! I am not sure that you waited for enough time before replying to your own thread. Here is some FAQ text from another forum and why I did not look at your thread until now.

Not everyone is reading every single post sequentially. When you bump your own post by replying to it yourself, you remove it from the "unanswered" or "zero replies" queue that some people use to search for questions. Also, when a thread has replies (even if it is just you), it looks like someone has already replied, so others who might be able to help will stay away.

For a solution, I would use the ControlSource property for textbox control first. You can set it manually at design time or as I did in code.

The 2nd part of the solution has to do with multiple conditional lookups. It looks like you might have tried an Autofilter sort of solution. That could work if you had a helper column. An Advanced Filter is another good way to do it. A standard Range Find method would work for the helper column approach as well. For my solution, I used an array formula method.

Private Sub UserForm_Initialize()
    TextBox1.ControlSource = Worksheets("Data").Range("R4").Address(External:=True)
    With Sheets("Data")
        ComboBox2.List = .Range("i2", .Range("I" & Rows.Count).End(xlUp)).Value
    End With
End Sub

'Client Name
Private Sub ComboBox1_Change()
    FillR4
End Sub

'Items Name
Private Sub ComboBox2_Change()
    FillR4
End Sub

'Filter Data by Comboboxes, fill Data!R4
Private Sub FillR4()
    Dim ws As Worksheet, r As Range, dr As Range, s As String, q As String

    Set ws = Worksheets("Data")
    Set r = ws.[c1].CurrentRegion   'Headers and Data Range
    Set dr = r.Offset(1).Resize(r.Rows.Count - 1)   'Data Range
   
    q = """"
    s = "=VLOOKUP(" & q & ComboBox1 & "|" & ComboBox2 & q & ",CHOOSE({1,2}," & _
        r.Columns(1).Address & "&" & q & "|" & q & "&" & r.Columns(2).Address & _
        "," & r.Columns(4).Address & "),2,0)"
    ws.[R4].FormulaArray = s
End Sub

 

[Hany ali]

very good,thanks alot
you are very Kind mr.Kenneth
Sorry,I apologize to your presence if issued anything contrary to the forum

 

[Hany ali]

Sorry mr.
Kenneth Hobson

i want the last price Not the max price
as the Result for this function

=IFERROR(LOOKUP(2,1/(($C$2:$C$1200=$R$2)*($D$2:$D$1200=$S$2)),$F$2:$F$1200),"")

 

[Marc L]

Hi !​

​

A last price demonstration :​

Sub Demo0()
       Dim Rg As Range
       Set Rg = Sheets("Data").UsedRange.Columns(1).Find("Ali", , xlValues, xlWhole, , xlPrevious)
    If Not Rg Is Nothing Then MsgBox Rg(1, 4).Value2: Set Rg = Nothing
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[Hany ali]

thanks alot mr Marc ,i want this code bring this Price in TextBox1
mr
Kenneth Hobson's Code was Right ,it's Bring the Max Price -but I want the Last Price

 

[Kenneth Hobson]

It is not contrary to the forum. It was just a tip to help you for your next thread.

If you want something other than the first found match, Advanced Filter might be best. I also attached the file as I had to create some values in cells for the Advanced Filter.

Private Sub UserForm_Initialize()
    With Sheets("Data")
        ComboBox2.List = .Range("i2", .Range("I" & Rows.Count).End(xlUp)).Value
        TextBox1.ControlSource = .[R4].Address(External:=True)
    End With
End Sub

'Client Name
Private Sub ComboBox1_Change()
    FillR4
End Sub

'Items Name
Private Sub ComboBox2_Change()
    FillR4
End Sub

'Filter Data by Comboboxes, fill Data!R4
Private Sub FillR4()
    Dim r As Range
    
    Application.ScreenUpdating = False
    'On Error GoTo endSub
    With Worksheets("Data")
        Set r = .Range("C1").CurrentRegion   'Headers and Data Range
        'Criteria filter values:
        .[S6] = ComboBox2
        .[R6] = ComboBox1
        r.AdvancedFilter xlFilterCopy, .[R5:S6], .[R7]
        .[R4] = .Cells(Rows.Count, "R").End(xlUp)
    End With
endSub:
    Application.ScreenUpdating = True
End Sub

 

[Hany ali]

Well done which is really required
God bless you