Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can't FIND value in HIDDEN column (xl2000)

    I have a small db in a worksheet, the column A of which is the field "ID." I have hidden the ID column as it is not needed by the user. I use the ID range in a macro to find & replace a record after editing.
    Here's the problem: when the column is hidden from view (or even with the width reduced to 1-2 pixels) the macro fails at the "Selection.Find........." command. As soon as I widen the column, the command works.
    What is happening?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Can't FIND value in HIDDEN column (xl2000)

    Your FIND works off of the SELECTION
    When col A is hidden, it is very difficult to SELECT it. I would guess that since it is hidden the user is NOT selecting the col and thus it is not searched.

    Give the find a range including column a and it should work fine.
    range("A:A").find
    OR
    range("A1:c200").find
    or whatever.

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't FIND value in HIDDEN column (xl2000)

    Steve,
    Thanks for the quick reply. The problem is not selecting the range. When the macro hangs, I have clicked Debug>>XL to get back to the worksheet, then made column A wider. The range is highlited. If I then step thru the macro it continues without a glitch.
    Here's the code


    Dim i as integer
    i = Range("NameCell").Offset(0, -1) 'NameCell contains the Name in Col B
    Range("dbIDs").Select
    Selection.FIND(What:=i, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate


    The command "Range("dbIDs").Select" works fine but the last line:
    "Selection.FIND........."
    fails only when the column width is too narrow. ( "Too narrow" may vary with the number of digits in the ID number. In testing this on records with 2 digits, if the column width was set to 2 it worked, but failed when set to 1.) It's as if the FIND only works when there is a visual confirmation, which seems very strange.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't FIND value in HIDDEN column (xl2000)

    It looks like the Find method will not operate on a range that contains hidden cells. Try this code:

    <pre>Dim i As Integer
    Dim oFindCell As Range
    i = Range("NameCell").Offset(0, -1) 'NameCell contains the Name in Col B
    Application.ScreenUpdating = False
    Range("dbIDs").EntireColumn.Hidden = False
    Set oFindCell = Range("dbIDs").Find(What:=i, After:=ActiveCell, LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    Range("dbIDs").EntireColumn.Hidden = True
    Application.ScreenUpdating = True
    If Not oFindCell Is Nothing Then
    MsgBox "Found ID in cell " & oFindCell.Address
    End If
    </pre>

    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't FIND value in HIDDEN column (xl2000)

    Thanks for the work around.

    Curious why FIND needs a visual?

Posting Permissions

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