vbs to open excel and go to a specific cell related to an input box in the vbs.

[m***@gmail.com]

Hi All,
I have been using the script below to open a particular sheet in an excel file. I then use the dins command to locate a part number so I can add a date to the row.

This is he script I use

Set objXl = CreateObject("Excel.Application")
Set ObjWB = objXl.Workbooks.Open("\\MSSLGBDC01\MSSLGBUserData\Shared Project Data\Project Tracking.xlsm")
objXl.Goto ObjWB.Sheets(2).Range("r1820")
objXl.Visible = True


I want the script which is a shorcut on the desktop to present an input box "Enter part number" and when the part number is entered to open the above spreadsheet at sheet 2 and locate the part number entered in the input box.
Specifically i want to be taken to a call in column R in the same row as the located part number.

So for example if I had a part number of 12345 in CELL B3 then i would want the active cell to be R3 where I could enter the date.

I am sure once I achieve this I will expand but this would be a great start.

Any help appreciated.

regards Russ

[GS]

Try...

Dim rng, sPartNum
sPartNum = InputBox("Enter part number")
If sPartNum = "" Then Exit Sub

Set objXl = CreateObject("Excel.Application")
Set ObjWB = objXl.Workbooks.Open("\\MSSLGBDC01\MSSLGBUserData\Shared
Project Data\Project Tracking.xlsm")
With ObjWB.Sheets(2)
Set rng = .Cells.Find(What:=sPartNum, After:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not rng Is Nothing Then objXl.Goto .Cells(rng.Row, "R"), True
End With
objXl.Visible = True

..where the 2nd arg for GoTo() scrolls the sheet so the cell is at the
top left of your screen. If you just want to scroll the part# row/col
into view (last row visible on your screen), omit the 2nd arg.