Userform w/textbox search a table for a value on click

I have a workbook/worksheet I wish to use to input Treasury futures prices in order to calculate a running P&L with. There is an Entry Price cell and an Exit Price cell which calculates and returns a money value into a P&L cell. I have all the math in place already. Treasury future quotes are as such: "121'050" with the next up tick being "120'055." These two quotes stand for 121 5/32 and 121 11/64. 121 is called the "handle" with 050 and 055 called the "tick".

I have a UserForm which opens when I click in any Entry Price or Exit Price cell on my spreadsheet. The UserForm has TextBox1 ("Handle") and TextBox2 ("Tick") and a CmdButton (OK). I have a table listing each "Tick" with the corresponding 32nd or 64th fractional value.

I need to run a VBA script so when I click the CmdButton (OK) the entered TextBox2 value (Tick) searches the table or range of cells for that Tick, then references the corresponding 32nd or 64th, then adds that fraction to the Handle value from TextBox1 and inserts the completed number (e.g. 121 5/32) into the Entry Price or Exit Price cell I originally clicked.

Thanks in advance for all help! (I am new to VBA and need a little help with this.)

Hi trader07,
Welcome to the forum.
To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Hi trader07
screen shot are a pain as no is going to duplicate the sheets
can zip or reduce the file size?
or expand on "searches the table or range of cells for that Tick, then references the corresponding 32nd or 64th" as it really does not mean anything when you dont know the layout.. bit of an art in asking a question and including the right infomation

There are two columns : Entry Price (H2:H100) and Exit Price (K2:K100).
When I click on any cell in either of those two columns a UserForm pops up.
There are two TextBoxes one named "Handle" (TextBox1) and one named "Tick" (TextBox2). Of course there is the CmndButn (OK).

After I have entered a text string in TextBox1 and TextBox2 I click the OK button. TextBox2 text string (050) needs to find/look/search through the range cells O2:O65 and find the matching text string; then grab the corresponding fractional value from the cell located range P2:P65 (e.g. TextBox2 value is 050 which is located in cell O11 and it corresponds with cell P11 with the fractional value 5/32.)

Next I need the fractional value of 5/32 to be added back to the TextBox1 text string (121 in this case) to give me a completed number with fraction, (e.g. the TextBox1 value was 121 so I need to end up with the number and fraction of 121 5/32).

Last I need the number and fraction 121 5/32 to insert into the original Entry Price (any cell in the range of H2:H100) or Exit Price (any cell in the range K2:K100) which I originally clicked to activate the UserForm. I have math that uses the number with fraction in the Entry Price and Exit Price cells which delivers to a Gross/Profit Loss cell.

Does this help any? I see your script above but don't get it.

Hi trader07
The code would be something like

Private Sub cmbFind_Click()
    Dim strFind As String    'what to find
    Dim FirstAddress As String
    Dim rSearch As Range  'range to search
    Set rSearch = Sheet1.Range("a6", Range("a65536").End(xlUp))
    Dim f      As Integer


    strFind = Me.TextBox1.Value    'what to look for

    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
            c.Select
            With Me    'load entry to form
                .TextBox2.Value = c.Offset(0, 1).Value
     
           End With
            FirstAddress = c.Address
            Do
                f = f + 1    'count number of matching records
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
            
        Else: MsgBox strFind & " not listed"    'search failed
        End If
    End With

Hi trader07,

try this for you command button code

Private Sub CmndButn_Click()
    Dim strFind As String
    Dim rSearch As Range
    
    Set rSearch = Sheet1.Range("O2:O65")
    
    strFind = Me.Tick.Value
    
    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
            c.Select
            Me.Handle.Value = Me.Handle.Value & " " & c.Offset(0, 1).Value
            ActiveCell.Value = Me.Handle.Value
        Else: MsgBox strFind & " not listed"       'search failed
        End If
    End With
    
End Sub

Hi trader07

can you attach a small sample file ?

no, just delete the sensitive infomation and any sheets not needed
you only need to leave a few columns

To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

On this page, below the message box, you will find a button labelled 'Manage Attachments'.
Clicking this button will open a new window for uploading attachments.

You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
Alternatively you can click the Attachment Icon to open this page.

To upload a file from your computer, click the 'Browse' button and locate the file.

To upload a file from another URL, enter the full URL for the file in the second box on this page.
Once you have completed one of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

can you zip the file?

trader07
I would use double click in the worksheet as its empty

Private Sub Worksheet_BeforeDoubleClick(ByVal _
                                        Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Me.Range("H2:H100,K2:K100")) Is Nothing Then
        UserForm1.Show
        Cancel = True
    End If
End Sub

and change the userform to

Private Sub OKButton1_Click()
Dim strFind As String
    Dim rSearch As Range
    Set rSearch = Sheet1.Range("O2:O65")
    strFind = Me.TextBox2.Value
    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
           ' c.Select
            Me.TextBox1.Value = Me.TextBox1.Value & " " & c.Offset(0, 1).Value
            ActiveCell.Value = Me.TextBox1.Value
            Else: MsgBox strFind & " not listed"       'search failed
                      
        End If
    End With
    End Sub

did not see the fraction format

Me.TextBox1.Value = Me.TextBox1.Value + c.Offset(0, 1).Value

probably format the textbox1 as fraction too