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


Subject: 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


"Joel" wrote:

I made a slight change in the code to handle a vairable amount of sheets

You can place as many sheet names as required into the line below.
ShtNames = Array("may_acct1", "may_acct2", "may_acct3")

All you need to do is to modify the line above putting in the number of
sheets you want to search. The first sheet results will go into column B,
the next in column C up to the number of sheets you put into the array
statement.

You also need to make sure the Main sheet name is corect in the statment
below. You can change the name in this line to match the summary sheet name

With Sheets("Main")


----------------------------------------------------------
Sub CreateaMain()

ShtNames = Array("may_acct1", "may_acct2", "may_acct3")
With Sheets("Main")
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


------------------------------------------------------------------------------------------

"Jane Doe" wrote:

Joel:

I appreciate the help, but this is not clear to me. I am not much good with
excel scripting. Can you explain in more detail where I need to change to
fit my worksheets (i.e. "For ShtNum = 0 to 2 do i need to change to my sheet
names I will be searching? may 08_acct1, june 08_acct2, may 08_acct2, june
08_acct3, etc...

Thank you!

"Joel" wrote:

Try this code

Sub CreateaMain()

ShtNames = Array("may_acct1", "may_acct2", "may_acct3")
With Sheets("Main")
LastRow = .Range("F" & Rows.Count).End(xlUp).Row
For ShtNum = 0 To 2
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

"Jane Doe" wrote:

Need to find value in column P of multiple worksheets (i.e. may_acct1,
may_acct2, may_acct3) based on phone number in column A of these worksheets
matching phone number in column F of main worksheet and return that value
from column P to column B,C, or D based on worksheet month (may_acct1,
june_acct1 or may_acct2, june_acct2).

Worksheet format to search

column
A................................................. ........................columnP
Subscriber
Number............................................ ...............Subscriber
Total



Worksheet format to return results

column A...(column B, C and D)..................column
E...................column F
Subscriber Total
Name May June July Active Carrier
Subscriber Number