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

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

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


--

Dave Peterson

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
If Not c Is Nothing Then
.Cells("B" & RowCount).Offset(0, ShtNum) = _
Sht.Range("P" & c.Row)<<<<

"Dave Peterson" wrote:

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

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


--

Dave Peterson

Change this portion:
If Not c Is Nothing Then
.Cells("B" & RowCount).Offset(0, ShtNum) = _
Sht.Range("P" & c.Row)<<<<

to

If Not c Is Nothing Then
msgbox c.row & vblf & sht.range("p" & c.row).text & vblf & sht.name
.Cells("B" & RowCount).Offset(0, ShtNum) = _
Sht.Range("P" & c.Row)<<<<

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.



Jane Doe wrote:

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
If Not c Is Nothing Then
.Cells("B" & RowCount).Offset(0, ShtNum) = _
Sht.Range("P" & c.Row)<<<<

"Dave Peterson" wrote:

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

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


--

Dave Peterson


--

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) = _

Dave Peterson wrote:

Change this portion:
If Not c Is Nothing Then
.Cells("B" & RowCount).Offset(0, ShtNum) = _
Sht.Range("P" & c.Row)<<<<

to

If Not c Is Nothing Then
msgbox c.row & vblf & sht.range("p" & c.row).text & vblf & sht.name
.Cells("B" & RowCount).Offset(0, ShtNum) = _
Sht.Range("P" & c.Row)<<<<

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.

Jane Doe wrote:

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
If Not c Is Nothing Then
.Cells("B" & RowCount).Offset(0, ShtNum) = _
Sht.Range("P" & c.Row)<<<<

"Dave Peterson" wrote:

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

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


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson