Excel/Excel

Advertisement


Question
Sir,
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.

Code:
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
        myR.EntireRow.Select
        With Range("B" & ActiveCell.Row)
        .Select
        End With
        ActiveCell.Hyperlinks(1).Follow
        
      Else
        MsgBox "Item Number Not Found. Please check The Number You have Entered"
        End If
        

      
      
    
      
End Sub

Answer
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:

http://www.yahoo.com

...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
        Else
             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

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jerry Beaucaire

Expertise

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

Experience

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)

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

Awards and Honors
Microsoft Excel MVP 2010

©2016 About.com. All rights reserved.