I have a master file containing 12000 item numbers in Column A and hyperlinked document for each item number in column B.
Now I have made a macro code (shown at the end) which is automatically display as soon as I scan the item number in Cell C1.
Now everything was working fine. But As I started creating real master file, Instead of hyperlinking by right clicking on cell, selecting hyperlink, and then choosing file location, I am hyperlinking it by using Hyperlink function as it is more convinient for me as all file locations are same and in same folder So I am just using =Hyperlink(D2&E2\) in B2 where D column contains file path and E columns contains part number.
Now hyperlink has been created as when I am clicking on it it is getting opened(So that is not a problem)
But now when I am running Macro it is showing me this error:
Run time error 9:subscript out of Range
and showing debug in  ActiveCell.Hyperlinks(1).Follow

Please Help.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("C1")) Is Nothing Then macro
End Sub
Sub macro()
    Dim myR As Range
    Set myR = Worksheets("Sheet1").Range("A:A").Find( _
        What:=Worksheets("Sheet1").Range("C1").Value, LookIn:=xlFormulas)
      If Not myR Is Nothing Then
        With Range("B" & ActiveCell.Row)
        End With
        MsgBox "Item Number Not Found. Please check The Number You have Entered"
        End If

End Sub

There's no need to select cells once you've "FOUND" them using the .FIND method in VBA.  You've stored that cell's location so you can send commands to it directly or to nearby cells.

Assuming column B includes the FULL path to these files including the http:// part at the beginnining, for instance:


...then you can use the FollowHyperlink method.   Here's a basic example based on your original code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myR As Range
    If Not Intersect(Target, Range("C1")) Is Nothing Then
        Set myR = Sheets("Sheet1").Range("A:A").Find(Target.Value, LookIn:=xlFormulas)
        If Not myR Is Nothing Then
             ThisWorkbook.FollowHyperlink myR.Offset(, 1).Value
             MsgBox "Item Number Not Found. Please check The Number You have Entered"
        End If
    End If
End Sub

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Jerry Beaucaire


Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files


Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files ===================== I have been offering free assistance as an Excel aid on many web sites for many years: (http://www.excelforum.com - JBeaucaire) ======== (http://www.askmehelpdesk.com/spreadsheets - JBeaucaire) ======= (http://www.mrexcel.com/forum - jbeaucaire)

Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition

Awards and Honors
Microsoft Excel MVP 2010

©2017 About.com. All rights reserved.