VBA to repeatedly search for the next occurrance of a value in a fixed cell

[Rich Seidner]

Hi--I'm a Total Newbie, and have spent literally days trying and failing to do this very very very simple thing.

A user of my sheet can type a value into a fixed cell location (let's say into cell H9), and I want to create a FindNext macro to find the next occurrence of whatever that value is in a column (let's say column B). I know I need to set a static variable to keep the address of the most recent cell that was found across repeated use of the FindNext macro, so as to start the next find operation after that ... but I have failed miserably.

Hoping someone can help me! And, yes, I intend to learn VBA properly.

 

[dchaney]

Hello Rich,

Welcome to the boards Can you please post the code you have been working on? It maybe a simple fix, plus it would allow us to see what you are doing and give advice for next time.

 

[Rich Seidner]

Hello Rich,

Welcome to the boards Can you please post the code you have been working on? It maybe a simple fix, plus it would allow us to see what you are doing and give advice for next time.

Well, this is a little ratty, because I've been trying various approaches, but I just got it working. YAY!

Thanks for your fabulous quick reply, and any comments on this are welcome.

PS: I now have one more question, but I will post that separately.

Sub searchLastName()
    Static foundtxt As String
    FTW = Worksheets("Contacts").Cells(9, "H").Value
    Start = "B20"
    foundtxt = Worksheets("Contacts").Cells(20, "A").Value
'    MsgBox FTW & foundtxt
    Dim rngFound As Range
 
Set rngFound = Sheets("Contacts").Columns(2).Find(What:=FTW, LookIn:=xlFormulas)
 
If Not rngFound Is Nothing Then
  'you found the value - do whatever
'  MsgBox FTW & foundtxt
    ActiveWorkbook.Worksheets("Contacts").Columns(2).Find(FTW, After:=Range(foundtxt)).Select
'    Cells(9, 8).Select
Else
  ' you didn't find the value
  If FTW = "enter last name" Then
    Else
   MsgBox "oops, """ & FTW & """ not found."
    End If
End If


    If FTW = "" Then
        Cells(9, 8).Select
        Cells(9, "H").Value = "enter last name"
        Worksheets(1).Activate
        ActiveWindow.Split = True
        ActiveWindow.Panes(1).ScrollRow = 19
    Else
    End If
    
End Sub

 

[dchaney]

What sheet number is your contacts sheet?

 

[Rich Seidner]

Thanks, you already answered my question over here: http://www.mrexcel.com/forum/excel-...-only-within-specific-column.html#post4609052.

"Contacts" is the name of the sheet where I'm doing the search. It's Sheet1 in the workbook.

 

[dchaney]

I was playing around with your code and cleaned it up a bit... I think this will work for ya... but if what you have already works may not be worth changing out...

Sub searchLastName()

Dim FTW As String
Dim ws As Worksheet
Dim rngFound As Range


Set ws = Sheets("Contacts")


    FTW = ws.Cells(9, "H").Value
 
Set rngFound = ws.Columns(2).Find(What:=FTW, LookIn:=xlFormulas)
 
    If FTW <> "" And Not rngFound Is Nothing Then 'you found the value - do whatever
        ws.Columns(2).Find(FTW, After:=Range("B1")).Select
        
    ElseIf FTW = "enter last name" Then MsgBox "oops, """ & FTW & """ not found within search area." ' you didn't find the value
    
    Else
        ws.Cells(9, "H").Value = "Enter Last Name"


        With ActiveWindow
            .SplitColumn = 8 'will perform split at column H
            .SplitRow = 9 'will perform split at row 9
            .Panes(1).ScrollRow = 19
        End With
        
    End If
    
End Sub

 

[Rich Seidner]

Wow--that's really nice of you. Thanks.

Since you're being so helpful, may i ask you a different question? I am trying to copy a specific sheet (a fixed filename) from another file (which is not currently open), and copy it to the current workbook. So that i can copy all of the data from that sheet. The trouble is, the various code snippets I've found online, just don't seem to work at all.

Do you have a code sample I could try?

 

[dchaney]

Do you have a code sample I could try?

I sure do This is snippets from a code I currently use, just review and change anything in red:

Sub MoveData()

With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
End With


    '****************************************************************************************
    'SET THIS WORKBOOK AND WORKSHEET VARIABLES                                                                      *
    '****************************************************************************************
    Set wbCurrent = ActiveWorkbook
    Set wsCurrent = wbCurrent.Sheets("[COLOR=#ff0000]Sheet1[/COLOR]") 'NEEDS TO BE THE NAME OF THE SHEET YOU ARE PASTING THE DATA TO
    
    nextRow = wsCurrent.Range("A" & Rows.Count).End(xlUp).Row + 1
    
    '****************************************************************************************
    'OPEN THE NEW FILE                                                                                                                *
    '****************************************************************************************
    On Error GoTo exitSub 'ERROR HANDLER
        Workbooks.Open "[COLOR=#ff0000]C:\Users\dchaney\Documents\MyFile.xlsm[/COLOR]" 'NEEDS TO BE CHANGED TO THE HARDCODED PATH AND FILE NAME YOU ARE OPENING
    On Error GoTo 0 'RESET THE ERROR HANDLER


    '****************************************************************************************
    'SET THE NEWLY OPENED WORKBOOK AND WORKSHEET VARIABLES                                  *
    '****************************************************************************************
    Set wbNew = ActiveWorkbook
    Set wsNew = wbNew.Sheets("[COLOR=#ff0000]Sheet1[/COLOR]") 'NEEDS TO BE CHANGED TO THE NAME OF THE SHEET WHERE THE DATA IS GETTING COPIED FROM
    
    lastRow = wsNew.Range("A" & Rows.Count).End(xlUp).Row


    '****************************************************************************************
    'COPY THE DATA FROM THE NEWLY OPENED WORKBOOK TO THE THIS WORKBOOK                      *
    '****************************************************************************************
    wsNew.Range("A2:[B][COLOR=#ff0000]Z[/COLOR][/B]" & lastRow).Copy 'NEEDS CHANGED TO THE COLUMN LETTER OF YOUR LAST USED COLUMN OF THE COPY DATA SHEET
    wsCurrent.Range("A" & nextRow).PasteSpecial
    Application.CutCopyMode = False
    
    wbNew.Close SaveChanges:=False
    
With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
End With


Exit Sub

 

[Rich Seidner]

Fabulous! I can't wait to try it tonight. Thanks ever so much.