Results 1 to 4 of 4

Amend macro to run through all sheets except two

Threaded View

rajsa Amend macro to run through... 02-05-2009, 10:46 AM
royUK Re: Amend macro to run... 02-05-2009, 11:11 AM
rajsa Re: Amend macro to run... 02-05-2009, 11:22 AM
royUK Re: Amend macro to run... 02-05-2009, 11:30 AM
  1. #1
    Registered User
    Join Date
    09-11-2008
    Location
    Scotland
    MS-Off Ver
    Excel 2002
    Posts
    8

    Amend macro to run through all sheets except two

    Hi everyone,

    I have found and adapted a macro (below) that searches all worksheets for a specific value (entered by the user in a specfic cell) then displays a list of hyperlinks to the matching cell locations.

    I now have a couple of sheets I don't want to be included in this macro ("Master List" and "Pivot Table") but I'm really struggling to find a way of amending the macro to exclude these. I've searched and tried a couple of ways but I get errors and I think I'm not knowledgable enough to know where to put each bit of code.

    Any help gratefully accepted

    Private Sub Search_Click()
    ' finds and displays list of locations for strain entered in Search!D10, hyperlinks to drawer map
    Range("C24:C500").Select
        Selection.Clear
    Dim findvar As String
    Dim sht
    Dim cl
    Dim outputvar As Long
    findvar = Sheets("SEARCH").Range("D10").Value
    outputvar = 0
    For Each sht In ActiveWorkbook.Sheets
       If sht.Name <> ActiveSheet.Name Then
           For Each cl In sht.UsedRange
               If InStr(cl.Value, findvar) <> 0 Then
                   Sheets("SEARCH").Range("C24").Offset(outputvar, 0).Formula = _
                           "=HYPERLINK(""#'" & cl.Parent.Name & "'!" & cl.Address & """,""" & cl.Parent.Name & " - " & cl.Value & """)"
                   outputvar = outputvar + 1
               End If
           Next
       End If
    Next
    Range("D10").Select
    End Sub
    Last edited by rajsa; 02-05-2009 at 11:24 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1