Excel Help

Thank you. I just downloaded the program, and it looks quite useful for a number of applications.

 

Well, I don't know about Bernoulli, but I'll be the chump.

Took about 25m after dinner. So use at your own peril.

Code:

Option Explicit
Private Const MACNAME As String = "Data Searcher"
Private Const DATA_NOT_FOUND As Integer = -1

Public Sub DataSearcher()
    Dim strFindThis As String
    Dim strFindInCol As String
    Dim col As Range

    Dim lngRowFound As Long
    Dim strMsg As String

    Dim lngLastRow As Long
    Dim strSearchRange As String

    On Error Resume Next

    'Get search criteria from user
    strFindThis = InputBox("Please enter the DATA to search for...", MACNAME)

    'eliminate trailing and leading spaces.
    strFindThis = Trim$(strFindThis)

    'catches user cancel too.
    If Len(strFindThis) = 0 Then GoTo NOT_VALID

    'Get column where criteria supposedly lives... same sequence as above
    strFindInCol = InputBox("Please enter the COLUMN(letter) to search in...", MACNAME)
    strFindInCol = Trim$(strFindInCol)
    If Len(strFindInCol) = 0 Then GoTo NOT_VALID

    'simple validation of column letter.
    If IsNumeric(strFindInCol) Then GoTo NOT_VALID

    'pet trick... try to create a range object with the col supplied by user.
    'if error occurs, column is not valid. Very nifty since there is no built-in
    'that converts column letter to column number.
    Set col = ActiveSheet.Range(strFindInCol & "1")
    If Err.Number <> 0 Then GoTo NOT_VALID

    'armed with a column, find the last *used* cell in that column.
    'this will be used for...
    '1) the range to search
    '2) if needed, which ROW to use for additions
    lngLastRow = col.SpecialCells(xlCellTypeLastCell).Row

    'make a *string* range argument ie B1:B789
    strSearchRange = strFindInCol & "1:" & strFindInCol & CStr(lngLastRow)

    'the search. Beware the hardcoded "Activesheet"
    'If activesheet is not a worksheet (ie chartsheet, macrosheet etc) this WILL fail!
    lngRowFound = SimpleSearch(strFindThis, ActiveSheet, strSearchRange)

    'process the result of the search
    If lngRowFound = DATA_NOT_FOUND Then
        strMsg = UCase$(strFindThis) & " was not found." & vbNewLine & vbNewLine
        strMsg = strMsg & "Do you want to add " & UCase$(strFindThis) & " to the end of the list?"

        Select Case MsgBox(strMsg, vbQuestion + vbYesNo, MACNAME)
            Case vbYes
                'add the item to last used row+1 of the chosen column
                ActiveSheet.Range(strFindInCol & lngLastRow).Offset(1).Value = UCase$(strFindThis)
            Case Else
        End Select
    Else
        strMsg = UCase$(strFindThis) & " exists in Row " & CStr(lngRowFound) & "." & vbNewLine & vbNewLine
        MsgBox strMsg, vbExclamation, MACNAME
    End If



EXIT_PROC:
    Set col = Nothing
    Exit Sub

NOT_VALID:
    strMsg = "Invalid or no input." & vbNewLine & vbNewLine
    strMsg = strMsg & "Data: " & strFindThis & vbNewLine
    strMsg = strMsg & "Column: " & strFindInCol
    MsgBox strMsg, vbExclamation, MACNAME
    GoTo EXIT_PROC

End Sub


Public Function SimpleSearch(ByVal strSearchValue As String, ws As Worksheet, strRng As String) As Long

    Dim x As Variant

    On Error Resume Next

    'NOTE: 9/2000 ... Per NG microsoft.public.excel.worksheetfunctions...
    '                 Match is not a member of WorsheetFunction as docs say.
    '                 Furthermore, when used in VBA, variant return must be used.
    '                 Apparantly there is an error with the #NA error Match returns to VBA
    x = Application.Match(strSearchValue, ws.Range(strRng), 0)
    If IsError(x) Then
        SimpleSearch = DATA_NOT_FOUND
    Else
        SimpleSearch = x
    End If

End Function

 

bump.

osorico, whoa, thanks! Now I'll just have to figure out how to put that whole thing together (wasn't really planning on using vba since I've no knowledge of it).

 

Hi USA;

steps are simple. This is 1 way to do it...

1) Open XL. Open YOUR workbook. Press ALT-F11 to open VBA environment.

2) Copy code from previous post.

3) Save copied code to a file. Name the file anything you want, but use a .BAS file extension.

4) Back in VBA environment, highlight your workbook, right click, and choose IMPORT. Obviously you want to import the .BAS file you created in step 3 above.

5) Save. Done. Close VBA environment.

To run the code, from the appropriate sheet in YOUR workbook press ALT-F8 (or Tools/Macros/Macros). Highlight the DataSearcher macro and RUN.

Kudos (or not) appreciated.

Osorico

 

I am not sure if you are still looking to get this working...

Here is the Excel with formula.

Just enter symbol in first cell (colored in Yellow) and double click anywhere on the sheet.

Hope it helps.

Press ALT+F11 to see the code.

 

Osoricy & Skyasa

Thank you very much for your help. The end result is exactly what I was looking for. I certainly didn't expect someone to write out coding in vb for it, but certainly is appreciated!

Kudos and appreciation to you both for the time.

USA

 

Hey no problem USATrader... It was very simple for me and thought might help.

 

It did. Thanks.

 

nice helpful info from each of you. did it and it works.

thanks!

 

Does anyone have an excel sheet that records your futures trades both short and long with the commission deduction?

I looked for one a while ago but never found anything.

Thanks,
tt

I'm not asking for free coding. If someone already has something like this I would be grateful to use it.