jo15765
12-07-2011, 12:26 PM
Using the code that mdmackillop provided, I want to take this a step further, and be able to cycle through worksheets from within the Active or Open workbook. Let's say that sheets Bacardi and Cola belong to Workbook2 and sheets "Alpha-Omega", "Jumbalaya", belong to workbook 1. When I add all 4 worksheet names into the ArrayTxt and all workbook names into the arr I get a debug error. That's why I was asking how to modify the code to allow for multiple workbooks/worksheets. Does that make sense what I am asking?
Sub Test()
Dim c As Range
Dim Rng As Range
Dim WsTData As Worksheet
Dim txt
Dim arr, a
ArrayTxt = Array("Alpha-Omega", "Jumbalaya", "Bacardi", "Cola")
arr = Array("Test1", "Test2", "Test3")
For Each a In arr
With Worksheets(a)
Set Rng = Range(.Cells(2, 1), .Cells(Rows.Count, 45).End(xlUp))
End With
For Each txt In ArrayTxt
DoFind Rng, txt
Next txt
Next a
End Sub
Function DoFind(Rng As Range, ToFind)
Dim FirstAddress As String
Dim c As Range
Set c = Rng.Find(ToFind, LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
'Act on found cell
c.Interior.ColorIndex = 45
Set c = Rng.FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End Function
The debug error that I get is Run-Time error '-2147352565
Excel cannot find the data you're searching for. If you are certain teh data exists in the current data sheet, check what you t yped and try again.
On this line of code:
With Worksheets(A)
Sub Test()
Dim c As Range
Dim Rng As Range
Dim WsTData As Worksheet
Dim txt
Dim arr, a
ArrayTxt = Array("Alpha-Omega", "Jumbalaya", "Bacardi", "Cola")
arr = Array("Test1", "Test2", "Test3")
For Each a In arr
With Worksheets(a)
Set Rng = Range(.Cells(2, 1), .Cells(Rows.Count, 45).End(xlUp))
End With
For Each txt In ArrayTxt
DoFind Rng, txt
Next txt
Next a
End Sub
Function DoFind(Rng As Range, ToFind)
Dim FirstAddress As String
Dim c As Range
Set c = Rng.Find(ToFind, LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
'Act on found cell
c.Interior.ColorIndex = 45
Set c = Rng.FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End Function
The debug error that I get is Run-Time error '-2147352565
Excel cannot find the data you're searching for. If you are certain teh data exists in the current data sheet, check what you t yped and try again.
On this line of code:
With Worksheets(A)