| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mismatch Error
I'm getting a type mismatch error on the following section of code - any ideas?
======== Set wsDest = Workbooks("NI Insurer Market Share as at " & Format(Date, "yyyy-mmmm") & ".xls").Worksheets(Array("PC (Chart)-NI-MONTH", _ "PC (Chart)-NI-YTD", "PC (Chart)-NI-R12", "HH (Chart)-NI-MONTH", _ "HH (Chart)-NI-YTD", "HH (Chart)-NI-R12", "CV (Chart)-NI-MONTH", _ "CV (Chart)-NI-YTD", "CV (Chart)-NI-R12")) ======== Thanks in advance |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Mismatch Error
I don't believe that you can set your wsDest variable (which I assume is
declared As Worksheet) to an array of multiple worksheets. You can assign only a single worksheet object to a Worksheet typed object variable. -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) "Sarah (OGI)" wrote in message ... I'm getting a type mismatch error on the following section of code - any ideas? ======== Set wsDest = Workbooks("NI Insurer Market Share as at " & Format(Date, "yyyy-mmmm") & ".xls").Worksheets(Array("PC (Chart)-NI-MONTH", _ "PC (Chart)-NI-YTD", "PC (Chart)-NI-R12", "HH (Chart)-NI-MONTH", _ "HH (Chart)-NI-YTD", "HH (Chart)-NI-R12", "CV (Chart)-NI-MONTH", _ "CV (Chart)-NI-YTD", "CV (Chart)-NI-R12")) ======== Thanks in advance |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Mismatch Error
Hi Sarah.
Perhaps you have dimmed the variable wsDest as Worksheets? Note that a Chart is included in the Sheets collection; it is not a worksheet and, therefore is not included in the Worksheets collection. Try: Dim wsDest as Sheets Additionally, try changing: Worksheets(Array.... to Sheets(Array --- Regards. Norman "Sarah (OGI)" wrote in message ... I'm getting a type mismatch error on the following section of code - any ideas? ======== Set wsDest = Workbooks("NI Insurer Market Share as at " & Format(Date, "yyyy-mmmm") & ".xls").Worksheets(Array("PC (Chart)-NI-MONTH", _ "PC (Chart)-NI-YTD", "PC (Chart)-NI-R12", "HH (Chart)-NI-MONTH", _ "HH (Chart)-NI-YTD", "HH (Chart)-NI-R12", "CV (Chart)-NI-MONTH", _ "CV (Chart)-NI-YTD", "CV (Chart)-NI-R12")) ======== Thanks in advance |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Mismatch Error
Hi Sarah,
Try: Dim wsDest as Sheets Or, if all the sheets of interest are charts: Dim wsDest as Charts --- Regards. Norman |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Mismatch Error
Dim wsDest as Sheets
I don't think that will solve anything. -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) "Norman Jones" wrote in message ... Hi Sarah. Perhaps you have dimmed the variable wsDest as Worksheets? Note that a Chart is included in the Sheets collection; it is not a worksheet and, therefore is not included in the Worksheets collection. Try: Dim wsDest as Sheets Additionally, try changing: Worksheets(Array.... to Sheets(Array --- Regards. Norman "Sarah (OGI)" wrote in message ... I'm getting a type mismatch error on the following section of code - any ideas? ======== Set wsDest = Workbooks("NI Insurer Market Share as at " & Format(Date, "yyyy-mmmm") & ".xls").Worksheets(Array("PC (Chart)-NI-MONTH", _ "PC (Chart)-NI-YTD", "PC (Chart)-NI-R12", "HH (Chart)-NI-MONTH", _ "HH (Chart)-NI-YTD", "HH (Chart)-NI-R12", "CV (Chart)-NI-MONTH", _ "CV (Chart)-NI-YTD", "CV (Chart)-NI-R12")) ======== Thanks in advance |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Mismatch Error
Hi Chip,
============ Dim wsDest as Sheets I don't think that will solve anything. ============ The following appeared to work for me: '========== Public Sub Tester() Dim wsDest As Sheets Dim i As Long Dim sStr As String sStr = "NI Insurer Market Share as at " _ & Format(Date, "yyyy-mmmm") Set wsDest = Workbooks(sStr).Sheets(Array( _ "PC (Chart)-NI-MONTH", _ "PC (Chart)-NI-YTD", _ "PC (Chart)-NI-R12", _ "HH (Chart)-NI-MONTH", _ "HH (Chart)-NI-YTD", _ "HH (Chart)-NI-R12", _ "CV (Chart)-NI-MONTH", _ "CV (Chart)-NI-YTD", _ "CV (Chart)-NI-R12")) For i = 1 To wsDest.Count Debug.Print wsDest(i).Name & vbTab _ & TypeName(wsDest(i)) Next i End Sub '<<========= --- Regards. Norman |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Mismatch Error
I tried the same thing and got a error. Go figure.
-- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) "Norman Jones" wrote in message ... Hi Chip, ============ Dim wsDest as Sheets I don't think that will solve anything. ============ The following appeared to work for me: '========== Public Sub Tester() Dim wsDest As Sheets Dim i As Long Dim sStr As String sStr = "NI Insurer Market Share as at " _ & Format(Date, "yyyy-mmmm") Set wsDest = Workbooks(sStr).Sheets(Array( _ "PC (Chart)-NI-MONTH", _ "PC (Chart)-NI-YTD", _ "PC (Chart)-NI-R12", _ "HH (Chart)-NI-MONTH", _ "HH (Chart)-NI-YTD", _ "HH (Chart)-NI-R12", _ "CV (Chart)-NI-MONTH", _ "CV (Chart)-NI-YTD", _ "CV (Chart)-NI-R12")) For i = 1 To wsDest.Count Debug.Print wsDest(i).Name & vbTab _ & TypeName(wsDest(i)) Next i End Sub '<<========= --- Regards. Norman |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Visual Basic Error Run Time Error, Type Mismatch | Excel Discussion (Misc queries) | |||
| runtime error 13 - type mismatch error in Excel 97 on Citrix | Excel Programming | |||
| xpath error? Runtime Error 13 type mismatch | Excel Discussion (Misc queries) | |||
| Conditional Formatting - Run Time Error '13' Type Mismatch Error | Excel Programming | |||
| Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming | |||
Linear Mode
