VBA : how to do a findnext on un-active sheet

  • Hi All:

    VBA is great but it sure can be perplexing.
    I am working on a project and I need some VBA-code to perform a "find" then a "findnext" using a userform. Does anyone have something along these lines that I can take a gander at see where I'm going wrong?

    In the code that I have used the variable that should receive the value to implement the "findnext" is not being detected.

    Any help I can get on this is greatly appreciated.

    code example:
    Private Sub FindIt_Click()
    ActiveSheet.Protect UserInterfaceOnly:=True, password:="engineer"
    Dim foundCell As Range
    Dim foundValue As Range
    Dim searchrng As Range
    Dim textToBeFound As String

    textToBeFound = TextBox5.Value
    Set searchrng = Sheets("Codes").Range("B773:B2638")
    'Find SIC Description and code along with NAICS description and code from input in "Find It" box

    With searchrng
    Set foundValue = .Cells.Find(What:=textToBeFound, _
    after:=(.Cells(.Cells.Count)), _
    LookIn:=xlValues, _
    LookAt:=xlPart, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    FindIt.Caption = "Next>>"
    CommandButton2.Caption = "<<Previous"

    Set foundCell = foundValue
    Set foundValue = searchrng.FindNext(after:=foundCell)

    'test to see if any matching text was found using an IS test

    If foundValue Is Nothing Then
    MsgBox "No Match Found! Either refine your description and try again, or consult the Source Industrial Code (SIC) table for the appropriate description."
    Exit Sub
    End If
    End With
    TextBox8.Value = foundValue 'loads SIC Description
    TextBox6.Value = foundValue.Offset(0, -1) 'loads SIC Code
    ActiveSheet.Range("f14").Value = foundValue.Offset(0, -1) ' loads search variable for "Next" and "Previous" arguments
    TextBox9.Value = foundValue.Offset(0, 2) 'loads NAICS Description
    TextBox7.Value = foundValue.Offset(0, 1) 'loads NAICS Code

    End Sub


  • Hi Chris

    Take a look at the code below. It uses the FIND method (as many times as needed) to bold the word "Cat" in Column "A"

    Note also that as it specifies the Sheet (I have used the Sheets CodeName) it can be run from any Worksheet

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!