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

ChrisBEE1 · Mar 17th 2004

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, _
MatchCase:=False)
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

TIA,
Chris:(:(:(

Dave Hawley · Mar 18th 2004

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

Code

Sub BoldCat()
Dim iLoop As Integer
Dim rNa As Range
Dim i As Integer


iLoop = WorksheetFunction.CountIf(Sheet1.Columns(1), "Cat")
Set rNa = Sheet1.Range("A1")


 For i = 1 To iLoop
  Set rNa = Sheet1.Columns(1).Find(What:="Cat", After:=rNa, _
             LookIn:=xlValues, LookAt:=xlWhole, _
             SearchOrder:=xlByRows, SearchDirection:=xlNext, _
             MatchCase:=True)
      rNa.Font.Bold = True
    
 Next i


End Sub

Display More

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

Participate now!

Share