Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jan 2005
    Location
    Colorado Springs, Colorado, USA
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Easy, but not for me (VB/Excel)

    I borrowed this code from a previous post and adjusted it to my circumstance. (I promise to give it back when finished). If there is a better way to do this please let me know, but..

    My real question is.... I can't get to the "ID Number" if it is found. (after ELSE statement at the bottom)

    Sub FindID()

    Dim rngSearch As Range, rngFound As Range
    Dim FindIDNum As Double

    ' Setting Variable FindIDNum to cell B2 of This Worksheet
    FindIDNum = Range("B2") ' < Can macro be initiated on "RETURN" after entering number in "B2" instead of "RUNNING" macro?

    ' Switching to Workbook that I want to search the number I typed in in cell B2
    Windows("BAUB Main Databank.xls").Activate

    ' column I want to search
    Set rngSearch = Range("E:E")

    ' Searching
    Set rngFound = rngSearch.Find(What:=FindIDNum, LookIn:=xlValues, LookAt:=xlWhole)

    ' Results
    If rngFound Is Nothing Then
    MsgBox "ID " & FindIDNum & " Not found"
    Else

    ' Code to take me to that number ************************

    End If

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Easy, but not for me (VB/Excel)

    does
    <code> rngFound.Activate</code>
    do what you want?

    StuartR

  3. #3
    Lounger
    Join Date
    Jan 2005
    Location
    Colorado Springs, Colorado, USA
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Easy, but not for me (VB/Excel)

    Thank you very much

    Do you know how I can activate the macro routine from "ENTER" after I type in the information?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Easy, but not for me (VB/Excel)

    If you want the code to run when you change the value of B2, you must call it from the Worksheet_Change event of the worksheet.
    Right-click the sheet tab of the worksheet.
    Select View Code from the shortcut menu that pops up.
    Copy the following code into the module that appears:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B2")) Is Nothing Then
    FindID
    End If
    End Sub

    The code checks if B2 is among the cells that have changed, and if so, it executes FindID.

  5. #5
    Lounger
    Join Date
    Jan 2005
    Location
    Colorado Springs, Colorado, USA
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Easy, but not for me (VB/Excel)

    thank you sir

Posting Permissions

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