Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2007

    Unanswered: Searching a list for a reference to use to select a worksheet

    Hi, I'm using the following code to try and match a cell to a corresponding worksheet.

    The name in the cell doesn't always match the worksheet name so I have a list to find the name in the cell and offset it by one column to get the name of the worksheet to use.

    If Trim(StName) <> "" Then
                        With Sheets("Sheet3").Range("A:A")
                        Set Rnge = .Find(What:=StName, _
                                    After:=.Cells(.Cells.Count), _
                                    LookIn:=xlValues, _
                                    LookAt:=xlWhole, _
                                    SearchOrder:=xlByRows, _
                                    SearchDirection:=xlNext, _
                            If Not Rnge Is Nothing Then
                                Application.Goto Rnge.Offset(0, 1), True
                            Set NameCell = Rnge.Offset(0, 1)
                            Set Name = Sheets("NameCell")
    The code works and selects the correct cell in the list using the offset, but I need to get the value in that cell to select the correct worksheet but it doesn't like Set Name = Sheets("NameCell")

    Any help will be greatly appreciated

  2. #2
    Join Date
    Apr 2004
    Derbyshire, UK
    Provided Answers: 2

    You haven't shown any dimention statements so we don't know what is what, I hope you are using Option Explicit at the top of the module!?
    I assume NameCell is a range object. If so, that is not necessary.

    Try this
    Dim ShtName as String
    ShtName = Rnge.Offest(0,1)

Posting Permissions

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