Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch Error in Excel VBA when looping through worksheets
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:D,E:E,I:I,J:J,N:N,O:O,P: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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch Error in Excel VBA when looping through worksheets
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 " wrote: 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:D,E:E,I:I,J:J,N:N,O:O,P: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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
runtime error 13 - type mismatch error in Excel 97 on Citrix | Excel Programming | |||
Type Mismatch Error in Excel VB on RowIndex | Excel Programming | |||
Conditional Formatting - Run Time Error '13' Type Mismatch Error | Excel Programming | |||
run time error(13) type mismatch in Excel Feed | Excel Programming | |||
Help: Compile error: type mismatch: array or user defined type expected | Excel Programming |