Open excel and find value, return next column

Question

I want to create a script opening a excel workscheet, find a value in column 'A' and returns the text in column 'B' ! Using VBA in iFIX from GE ! When I want to declare 'MyValue' as range, he gives a fault message. Range is unknown ! This is my code to return the same column. Someone to give another solution?

Private Sub OPEN_MSG_Click()
    Dim FindString$
    Dim MyValue   
    Dim objExcel, objsheet, WB,WS As Object

    'Open excel file
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = False

    Set WB = objExcel.Workbooks.Open("C:\Program Files (x86)\Proficy\Proficy iFIX\ProjectBackup\Foutcode_Opgezuiverd.xlsx")
    WB.ACTIVATE

    Set WS = WB.Worksheets("Blad1")
    'Set objsheet = objExcel.ActiveWorkbook.Worksheets(1)

    FindString = InputBox("Enter a Search value")

    With WS.Range("A1:A800")
        Set MyValue = .Find("FindString", LookIn:=xlValues)
        If Not MyValue Is Nothing Then
            MsgBox MyValue.Column
        MsgBox MyValue.row
        End If
    End With

    ' Save the spreadsheet and close the workbook.
    objExcel.ActiveWorkbook.Close
    ' Quit Excel.
    objExcel.Application.Quit

End Sub

Does it work or not? Range is only a datatype in Excel. Just leave it as Variant. — Plagon
– Plagon, Commented Aug 10, 2017 at 9:09

Slaqr · Accepted Answer · 2017-08-10 10:52:11Z

1

In the find method you ask it to search for "FindString" as a string. Since you want to find the value within FindString you should remove the "".

    Set MyValue = .Find(FindString, LookIn:=xlValues)

You might also want to declare Findstring as a string to avoid errors with the find method.

answered Aug 10, 2017 at 10:52

Slaqr

5634 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Open excel and find value, return next column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related