Search/Update in multiple worksheets VBA code help

utalwalk · April 13th, 2006, 01:25 PM

Hi folks:

I'm looking for some code help in Excel VBA to write a subroutine to lookup a text string in a defined range of cells say A4:AZ4 in all 3 sheets. My requirement is that if the string is not found then it should write that content in sheet 4 with a comment. However, if found then the code would update a column next to the found cell(row,col) with a text string.

Has anyone tried something simillar? If so, would you like to share your ideas?

maccas · April 19th, 2006, 04:42 AM

This seems to do the job:

Code:

Public Sub Main()

Dim strSearchRange As String
Dim shSearchSheets As New Collection
Dim SearchSheet As Worksheet
Dim OutSheet As Worksheet
Dim strToFind As String
Dim Cell As Range

    strSearchRange = "A4:AZ4"

    shSearchSheets.Add Item:=ThisWorkbook.Sheets("Sheet1")
    shSearchSheets.Add Item:=ThisWorkbook.Sheets("Sheet2")
    shSearchSheets.Add Item:=ThisWorkbook.Sheets("Sheet3")

    Set OutSheet = ThisWorkbook.Sheets("Sheet4")
    strToFind = "Test"

    For Each SearchSheet In shSearchSheets

        Set Cell = SearchSheet.Range(strSearchRange).Find(strToFind)

        If Not Cell Is Nothing Then
            SearchSheet.Columns(Cell.Column).Insert Shift:=xlRight
            Cell.Offset(0, -1).Value = "Found it!"
            Exit Sub
        End If

    Next SearchSheet

    OutSheet.Cells(1, 1).Value = strToFind
    OutSheet.Cells(1, 1).ClearComments
    With OutSheet.Cells(1, 1).AddComment
        .Visible = False
        .Text Text:="Couldn't find this"
    End With

End Sub