Is there a limit?

[Jane Doe]

Is there a limit to the amount of shtNames= I can have in the array in the
script below?


Subject: RE: Search multiple worksheets and return value to main worksheet
9/11/2008 2:05 PM PST

By: Jane Doe In: microsoft.public.excel.programming


I received a Runtime 13 type "mismatch error" ???

Sub CreateaMain()

ShtNames = Array("May 08_478156199", "May 08_4614445456", "June
08_478156199", "June 08_461445456", "July 08_478156199", "July 08_461445456")
With Sheets("Phones_Analysis_9-2008")
LastRow = .Range("F" & Rows.Count).End(xlUp).Row
For ShtNum = LBound(ShtNames) To UBound(ShtNames)
Set Sht = Sheets(ShtNames(ShtNum))
For RowCount = 1 To LastRow
PhoneNum = .Range("F" & RowCount)
Set c = Sht.Columns("A").Find(what:=PhoneNum, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
.Cells("B" & RowCount).Offset(0, ShtNum) = _
Sht.Range("P" & c.Row)
End If
Next RowCount
Next ShtNum
End With
End Sub

 

[Dave Peterson]

That's not the problem.

Which line caused the error?

If you have errors in your data (#value!, #div/0!, ...), then that could cause
the trouble.

I'd look in the phone number (column F) and I'd look at column P, too.

 

[Jane Doe]

This was just a general question. I was wondering if there is a limit in the
sheets to search in the array.

I know little to nothing about VB, another user wrote this. The error that
I was discussing in another post was a result of that users script. Another
user suggest Dim c?

Anyway, the error in question on the other post you are talking about is

 

[Dave Peterson]

Change this portion:

to

This line will show you the row where the phone number was found, the text
that's in that row in colum P (I still think it's an error) and the name of the
sheet to inspect.

====
Declaring your variables and specifying the properties (not relying on the
default property) is always a good idea (in my thinking). But in this case,
these are not the problems.

 

[Dave Peterson]

Joel found the typing error in one of your other threads.

..Cells("B" & RowCount).Offset(0, ShtNum) = _
should be:
..range("B" & RowCount).Offset(0, ShtNum) = _