Type Mismatch Error in Excel VBA when looping through worksheets

[scotto56]

Check the FOR NEXT Loop - four lines into loop (see *****...)- getting
type mismatch error - any thoughts/advice appreciated. Just looping
though selecting columns and then two additional columns without the
headers. Eventually in order to change to Proper Case. Thanks! -
Scott

Application.ScreenUpdating = False
Dim myRng As Range
Dim myArea As Range
Dim wsSheet As Worksheet
On Error Resume Next

For Each wsSheet In Worksheets
wsSheet.Select
Set myRng = Range("D,E:E,I:I,J:J,N:N,O:O,P,Q:Q,Y2:Z2")
myRng.Select
' *********THESE NEXT TWO LINES WILL CAUSE THE ERROR 13 - TYPE
MISMATCH
myRng("Y2").Activate
myRng(Selection, Selection.End(xlDown)).Select

If myRng Is Nothing Then
MsgBox "Nothing in intersect range"
Else
For Each myArea In myRng.Areas
myArea.Formula = Application.Proper(myArea.Formula)
Next myArea
End If

Range("A1").Select
Next wsSheet

Application.ScreenUpdating = True

Sheets("Master").Select

 

[Tom Hutchins]

myRng is a Range object which is set to contain 8 whole columns + 2 cells.
myRng("Y2") is syntactically incorrect - it didn't give me an error, but it
didn't do anything, either. Use Range("Y2") instead. You are using .Activate,
which is not the same thing as .Select, so your next statement is not doing
what you intended. Is your intent for column Y to select Y2 and extend the
selection down through all (contiguous) cells with data? If so, use

Range("Y2").Select
Range(Selection, Selection.End(xlDown)).Select

This will not add the selected cells in column Y to myRng, so you will need
to process this selection separately from myRng (maybe assign it to another
Range variable).

Because myRng has an assigned value, your "If myRng is Nothing" statement
will always be false. If you want to test if myRng contains anything, use
something like
If Application.WorksheetFunction.CountA(myRng) = 0 Then

Hope this helps,

Hutch