Hi there,
See if the following code does what you need:
Option Explicit
Sub FindID()
Const sDATA_RANGE As String = "A1:A4"
Dim lRowNumber As Long
Dim sFileName As String
Dim objExcel As Excel.Application
Dim sInput As String
Dim wks As Worksheet
sInput = InputBox("Please enter Name to be located")
Set objExcel = CreateObject("Excel.Application")
With objExcel
.Visible = False
sFileName = "C:\Users\Desktop\test1.xlsx"
.Workbooks.Open sFileName
Set wks = .ActiveSheet
lRowNumber = -999
On Error Resume Next
lRowNumber = .Match(sInput, wks.Range(sDATA_RANGE), 0)
On Error GoTo 0
If lRowNumber > 0 Then
MsgBox wks.Range(sDATA_RANGE).Cells(lRowNumber, 1).Offset(0, 1).Value
Else: MsgBox "The value """ & sInput & """ could not be located " & _
"in the Range """ & sDATA_RANGE & """", vbExclamation
End If
.Quit
End With
Set objExcel = Nothing
End Sub
The highlighted value may be altered to suit your own requirements.
I assume that you need this routine to create a temporary new instance of Excel - if you don't need a new instance the code can be made significantly shorter.
Hope this helps - please let me know how you get on.
Regards,
Greg M
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks