|
Subject:
|
Search/Update in multiple worksheets VBA code help
|
|
Posted By:
|
utalwalk
|
Post Date:
|
4/13/2006 1:25:42 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?
|
|
Reply By:
|
maccas
|
Reply Date:
|
4/19/2006 4:42:49 AM
|
This seems to do the job:
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
|
