array - type mismatch
 

I have the following section of code which is giving me a "type
mismatch" error at:
== For CapsSheets = LBound(CapsSheetarr) To UBound(CapsSheetarr)

Can anyone help with this?
Thanks...


Dim CapsSheetarr As Variant
Dim CapsSheets As Integer
Dim tlcapscell As Range
Dim blcapscell As Range
Dim brcapscell As Range
Dim capsrange As Range

Set CapsSheetarr = Sheets(Array("Metro AHK Base", "Metro AHK
Complete"))
For CapsSheets = LBound(CapsSheetarr) To UBound(CapsSheetarr)
With Worksheets("" & CapsSheetarr(CapsSheets))
.Select
Set tlcapscell = .Cells(3, 2)
Set blcapscell = .Cells(Rows.Count,
tlcapscell.Column).End(xlUp)
Set brcapscell = .Cells(blcapscell.Row,
blcapscell.Column).End(xlToRight)
Set capsrange = Range(tlcapscell, brcapscell)
End With
Next

array - type mismatch
 

Hi
Take away the Sheets bit and leave
Array("Metro AHK Base", "Metro AHK Complete")

regards
Paul
On Mar 12, 7:42*pm, Steve wrote:
I have the following section of code which is giving me a "type
mismatch" error at:
== For CapsSheets = LBound(CapsSheetarr) To UBound(CapsSheetarr)

Can anyone help with this?
Thanks...

* * Dim CapsSheetarr As Variant
* * Dim CapsSheets As Integer
* * Dim tlcapscell As Range
* * Dim blcapscell As Range
* * Dim brcapscell As Range
* * Dim capsrange As Range

* * Set CapsSheetarr = Sheets(Array("Metro AHK Base", "Metro AHK
Complete"))
* * * * For CapsSheets = LBound(CapsSheetarr) To UBound(CapsSheetarr)
* * * * * * With Worksheets("" & CapsSheetarr(CapsSheets))
* * * * * * * * .Select
* * * * * * * * Set tlcapscell = .Cells(3, 2)
* * * * * * * * Set blcapscell = .Cells(Rows.Count,
tlcapscell.Column).End(xlUp)
* * * * * * * * Set brcapscell = .Cells(blcapscell.Row,
blcapscell.Column).End(xlToRight)
* * * * * * * * Set capsrange = Range(tlcapscell, brcapscell)
* * * * * * End With
* * * * Next

array - type mismatch
 

Set CapsSheetarr = Array("Metro AHK Base", "Metro AHK Complete"))
For CapsSheets = LBound(CapsSheetarr) To UBound(CapsSheetarr)
With Worksheets(CapsSheetarr(CapsSheets))
.Select
Set tlcapscell = .Cells(3, 2)
Set blcapscell = .Cells(.Rows.Count, tlcapscell.Column).End(xlUp)
Set brcapscell _
= .Cells(blcapscell.Row, blcapscell.Column).End(xlToRight)
Set capsrange = .Range(tlcapscell, brcapscell)
End With
Next CapsSheets



Steve wrote:

I have the following section of code which is giving me a "type
mismatch" error at:
== For CapsSheets = LBound(CapsSheetarr) To UBound(CapsSheetarr)

Can anyone help with this?
Thanks...

Dim CapsSheetarr As Variant
Dim CapsSheets As Integer
Dim tlcapscell As Range
Dim blcapscell As Range
Dim brcapscell As Range
Dim capsrange As Range

Set CapsSheetarr = Sheets(Array("Metro AHK Base", "Metro AHK
Complete"))
For CapsSheets = LBound(CapsSheetarr) To UBound(CapsSheetarr)
With Worksheets("" & CapsSheetarr(CapsSheets))
.Select
Set tlcapscell = .Cells(3, 2)
Set blcapscell = .Cells(Rows.Count,
tlcapscell.Column).End(xlUp)
Set brcapscell = .Cells(blcapscell.Row,
blcapscell.Column).End(xlToRight)
Set capsrange = Range(tlcapscell, brcapscell)
End With
Next

--

Dave Peterson

array - type mismatch
 

Steve,

Set up your array like this

CapsSheetarr = Array("Metro AHK Base", "Metro AHKComplete")

instead of

Set CapsSheetarr = Sheets(Array("Metro AHK Base", "Metro AHK Complete"))

Mike


"Steve" wrote:

I have the following section of code which is giving me a "type
mismatch" error at:
== For CapsSheets = LBound(CapsSheetarr) To UBound(CapsSheetarr)

Can anyone help with this?
Thanks...


Dim CapsSheetarr As Variant
Dim CapsSheets As Integer
Dim tlcapscell As Range
Dim blcapscell As Range
Dim brcapscell As Range
Dim capsrange As Range

Set CapsSheetarr = Sheets(Array("Metro AHK Base", "Metro AHK
Complete"))
For CapsSheets = LBound(CapsSheetarr) To UBound(CapsSheetarr)
With Worksheets("" & CapsSheetarr(CapsSheets))
.Select
Set tlcapscell = .Cells(3, 2)
Set blcapscell = .Cells(Rows.Count,
tlcapscell.Column).End(xlUp)
Set brcapscell = .Cells(blcapscell.Row,
blcapscell.Column).End(xlToRight)
Set capsrange = Range(tlcapscell, brcapscell)
End With
Next