Solved: Assistance Modifying Array

[jo15765]

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)

[Kenneth Hobs]

Test1 is a worksheet name. You said that the values in arr() are workbook names.

I don't see where you have opened the workbooks in arr(). When you use find, you will probably need to activate the workbooks first.

[jo15765]

My apologies you are correct. I am wanting trying to be able to re-use this function regardless of what workbook I have open as long as the worksheet names are contained within the arr Array.

So Kenneth you are correct on my mistake...the array ArrayTxt are names that appear within the worksheet and the array arr are worksheet names.

What would need to be modified so this function can be re-used from workbook to workbook as long as the worksheet names are contained within the array arr?

[Bob Phillips]

Seeing as there seems to be no reference to a workbook name, doesn't it work on the Activeworkbook, regardless of what that is?

[jo15765]

There is no reference to a set workbook name, it pulls the data from the active workbook. How would the code need to be modified to search for the Arraytxt, and if it is not found in the ActiveWorkbook then "Do Nothing"

The code works perfectly if the Arraytxt is found, but it will throw an error if the Arraytxt is not found.

[Bob Phillips]

Where does it throw the error? The code seems to handle a not found situation.

[Kenneth Hobs]

I added a "." to the set Rng line and the function to see if the sheet exists.

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
If WorkSheetExists(a) Then
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
End If
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

'WorkSheetExists in a workbook:
Function WorkSheetExists(sWorkSheet As String, Optional sWorkbook As String = "") As Boolean
Dim ws As Worksheet, wb As Workbook
On Error GoTo notExists
If sWorkbook = "" Then
Set wb = ActiveWorkbook
Else
Set wb = Workbooks(sWorkbook)
End If
Set ws = wb.Worksheets(sWorkSheet)
WorkSheetExists = True
Exit Function
notExists:
WorkSheetExists = False
End Function

[jo15765]

Kenneth thank you for the provided code...When I add it to my workbook, I get a compile error of:
Compile Error:
ByRef argument type mismatch

on this line:

If WorkSheetExists(A) Then

In the Function Test

[Kenneth Hobs]

It is telling you that you need to convert a variant to a string. Be sure to put the function in a Module.

If WorkSheetExists(cstr(A)) Then

[jo15765]

That did it perfectly! Thank you for the assistance and support.