| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type mismatch error - 'run-time error 13'
I have some code running in a worksheet (sheet 1) to hide two worksheets
unless the value (text) in a cell (D4:E4 merged) of 'sheet 1' is a certain value. The value in 'D4:E4' is chosen from a list of 4 options (set up using a data validationlist). I am getting a type mismatch error if the value in 'd4' is deleted/cleared (not much of an issue as should only select form the list). More of an issue is I get the same error when any other merged cells (i.e. "C2:I2", "B18:J20") are cleared (i.e select cell and hit 'Delete'). Any ideas on why the error occurs or how to avoid it? The code: Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range("D4") Then If Target.Value = "SMART Cable" Then Sheets("Load v Distance").Visible = True Sheets("Load v Time").Visible = True Else Sheets("Load v Distance").Visible = False Sheets("Load v Time").Visible = False End If End If End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Type mismatch error - 'run-time error 13'
Try with the below.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$D$4:$E$4" Then If Range("D4") = "SMART Cable" Then Sheets("Sheet2").Visible = True Sheets("Sheet3").Visible = True Else Sheets("Sheet2").Visible = False Sheets("Sheet3").Visible = False End If End If End Sub -- If this post helps click Yes --------------- Jacob Skaria "Al" wrote: I have some code running in a worksheet (sheet 1) to hide two worksheets unless the value (text) in a cell (D4:E4 merged) of 'sheet 1' is a certain value. The value in 'D4:E4' is chosen from a list of 4 options (set up using a data validationlist). I am getting a type mismatch error if the value in 'd4' is deleted/cleared (not much of an issue as should only select form the list). More of an issue is I get the same error when any other merged cells (i.e. "C2:I2", "B18:J20") are cleared (i.e select cell and hit 'Delete'). Any ideas on why the error occurs or how to avoid it? The code: Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range("D4") Then If Target.Value = "SMART Cable" Then Sheets("Load v Distance").Visible = True Sheets("Load v Time").Visible = True Else Sheets("Load v Distance").Visible = False Sheets("Load v Time").Visible = False End If End If End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Type mismatch error - 'run-time error 13'
This should help:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("D4")) Is Nothing Then If Target.Cells(1, 1).Value = "SMART Cable" Then Sheets("Load v Distance").Visible = True Sheets("Load v Time").Visible = True Else Sheets("Load v Distance").Visible = False Sheets("Load v Time").Visible = False End If End If End Sub -- Regards, Tim Zych http://www.higherdata.com Workbook Compare - Excel data comparison utility "Al" wrote in message ... I have some code running in a worksheet (sheet 1) to hide two worksheets unless the value (text) in a cell (D4:E4 merged) of 'sheet 1' is a certain value. The value in 'D4:E4' is chosen from a list of 4 options (set up using a data validationlist). I am getting a type mismatch error if the value in 'd4' is deleted/cleared (not much of an issue as should only select form the list). More of an issue is I get the same error when any other merged cells (i.e. "C2:I2", "B18:J20") are cleared (i.e select cell and hit 'Delete'). Any ideas on why the error occurs or how to avoid it? The code: Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range("D4") Then If Target.Value = "SMART Cable" Then Sheets("Load v Distance").Visible = True Sheets("Load v Time").Visible = True Else Sheets("Load v Distance").Visible = False Sheets("Load v Time").Visible = False End If End If End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Type mismatch error - 'run-time error 13'
Hi Jacob, thanks for the quick reply however that code does not hide the
sheets. "Jacob Skaria" wrote: Try with the below. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$D$4:$E$4" Then If Range("D4") = "SMART Cable" Then Sheets("Sheet2").Visible = True Sheets("Sheet3").Visible = True Else Sheets("Sheet2").Visible = False Sheets("Sheet3").Visible = False End If End If End Sub -- If this post helps click Yes --------------- Jacob Skaria "Al" wrote: I have some code running in a worksheet (sheet 1) to hide two worksheets unless the value (text) in a cell (D4:E4 merged) of 'sheet 1' is a certain value. The value in 'D4:E4' is chosen from a list of 4 options (set up using a data validationlist). I am getting a type mismatch error if the value in 'd4' is deleted/cleared (not much of an issue as should only select form the list). More of an issue is I get the same error when any other merged cells (i.e. "C2:I2", "B18:J20") are cleared (i.e select cell and hit 'Delete'). Any ideas on why the error occurs or how to avoid it? The code: Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range("D4") Then If Target.Value = "SMART Cable" Then Sheets("Load v Distance").Visible = True Sheets("Load v Time").Visible = True Else Sheets("Load v Distance").Visible = False Sheets("Load v Time").Visible = False End If End If End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Type mismatch error - 'run-time error 13'
Tim,
that works a treat, thanks very much. Can't quite understand how but it works. Cheers. "Tim Zych" wrote: This should help: Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("D4")) Is Nothing Then If Target.Cells(1, 1).Value = "SMART Cable" Then Sheets("Load v Distance").Visible = True Sheets("Load v Time").Visible = True Else Sheets("Load v Distance").Visible = False Sheets("Load v Time").Visible = False End If End If End Sub -- Regards, Tim Zych http://www.higherdata.com Workbook Compare - Excel data comparison utility "Al" wrote in message ... I have some code running in a worksheet (sheet 1) to hide two worksheets unless the value (text) in a cell (D4:E4 merged) of 'sheet 1' is a certain value. The value in 'D4:E4' is chosen from a list of 4 options (set up using a data validationlist). I am getting a type mismatch error if the value in 'd4' is deleted/cleared (not much of an issue as should only select form the list). More of an issue is I get the same error when any other merged cells (i.e. "C2:I2", "B18:J20") are cleared (i.e select cell and hit 'Delete'). Any ideas on why the error occurs or how to avoid it? The code: Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range("D4") Then If Target.Value = "SMART Cable" Then Sheets("Load v Distance").Visible = True Sheets("Load v Time").Visible = True Else Sheets("Load v Distance").Visible = False Sheets("Load v Time").Visible = False End If End If End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Type mismatch error - 'run-time error 13'
Change the sheet names from Sheet2,Sheet3 to to "Load v Distance" & "Load v
Time" If this post helps click Yes --------------- Jacob Skaria "Al" wrote: Tim, that works a treat, thanks very much. Can't quite understand how but it works. Cheers. "Tim Zych" wrote: This should help: Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("D4")) Is Nothing Then If Target.Cells(1, 1).Value = "SMART Cable" Then Sheets("Load v Distance").Visible = True Sheets("Load v Time").Visible = True Else Sheets("Load v Distance").Visible = False Sheets("Load v Time").Visible = False End If End If End Sub -- Regards, Tim Zych http://www.higherdata.com Workbook Compare - Excel data comparison utility "Al" wrote in message ... I have some code running in a worksheet (sheet 1) to hide two worksheets unless the value (text) in a cell (D4:E4 merged) of 'sheet 1' is a certain value. The value in 'D4:E4' is chosen from a list of 4 options (set up using a data validationlist). I am getting a type mismatch error if the value in 'd4' is deleted/cleared (not much of an issue as should only select form the list). More of an issue is I get the same error when any other merged cells (i.e. "C2:I2", "B18:J20") are cleared (i.e select cell and hit 'Delete'). Any ideas on why the error occurs or how to avoid it? The code: Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range("D4") Then If Target.Value = "SMART Cable" Then Sheets("Load v Distance").Visible = True Sheets("Load v Time").Visible = True Else Sheets("Load v Distance").Visible = False Sheets("Load v Time").Visible = False End If End If End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Type mismatch error - 'run-time error 13'
Please try the below and feedback. Variable lngLastRow will have the last row
Sub test() Dim lngLastRow As Long With ActiveWorkbook.Sheets("Database") lngLastRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1 ..Range("A" & lngLastRow) = ActiveSheet.Range("E1") ..Range("B" & lngLastRow) = ActiveSheet.Range("B3") ..Range("C" & lngLastRow) = ActiveSheet.Range("B1") ..Range("G" & lngLastRow) = ActiveSheet.Range("E5") ..Range("J" & lngLastRow) = ActiveSheet.Range("B55") ..Range("K" & lngLastRow) = ActiveSheet.Range("E2") End With End Sub -- If this post helps click Yes --------------- Jacob Skaria "Al" wrote: Tim, that works a treat, thanks very much. Can't quite understand how but it works. Cheers. "Tim Zych" wrote: This should help: Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("D4")) Is Nothing Then If Target.Cells(1, 1).Value = "SMART Cable" Then Sheets("Load v Distance").Visible = True Sheets("Load v Time").Visible = True Else Sheets("Load v Distance").Visible = False Sheets("Load v Time").Visible = False End If End If End Sub -- Regards, Tim Zych http://www.higherdata.com Workbook Compare - Excel data comparison utility "Al" wrote in message ... I have some code running in a worksheet (sheet 1) to hide two worksheets unless the value (text) in a cell (D4:E4 merged) of 'sheet 1' is a certain value. The value in 'D4:E4' is chosen from a list of 4 options (set up using a data validationlist). I am getting a type mismatch error if the value in 'd4' is deleted/cleared (not much of an issue as should only select form the list). More of an issue is I get the same error when any other merged cells (i.e. "C2:I2", "B18:J20") are cleared (i.e select cell and hit 'Delete'). Any ideas on why the error occurs or how to avoid it? The code: Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range("D4") Then If Target.Value = "SMART Cable" Then Sheets("Load v Distance").Visible = True Sheets("Load v Time").Visible = True Else Sheets("Load v Distance").Visible = False Sheets("Load v Time").Visible = False End If End If End Sub |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Type mismatch error - 'run-time error 13'
Glad to help.
To explain it a bit mo If Not Application.Intersect(Target, Range("D4")) Is Nothing Then The line above makes sure that the changed-cell intersects with D4 before evaluating anything. The other issue was when clearing D4, and since D4 was merged with E4, it resulted in a change to 2 cells. Investigate it and you'll see the issue: Here is the code with a Stop. Enter a value, then clear it and it will stop at the stop point. Step through and debug.print the address. Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("D4")) Is Nothing Then If Target.Cells.Count 1 Then Stop '< -- when clearing D4 it will stop here. Debug.Print Target.Address '< -- results in $D$4:$E$4 because it is merged. If Target.Cells(1, 1).Value = "SMART Cable" Then '< -- top left cell, for single or multi cells. Sheets("Load v Distance").Visible = True Sheets("Load v Time").Visible = True Else Sheets("Load v Distance").Visible = False Sheets("Load v Time").Visible = False End If End If End Sub "Target.Cells(1,1)" returns the top-left cell of a single or muti-cell range. Normally, Target is only a single cell. But when it is part of a merged area, it sometimes evaluates as more than one cell -- like when clearing D4. Cells(1,1) returns the top left cell. When using merged cells, workaround code such as this is needed. But the code is safe enough to continue to work even if you decide to un-merge D4:E4. -- Regards, Tim Zych http://www.higherdata.com Workbook Compare - Excel data comparison utility "Al" wrote in message ... Tim, that works a treat, thanks very much. Can't quite understand how but it works. Cheers. "Tim Zych" wrote: This should help: Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("D4")) Is Nothing Then If Target.Cells(1, 1).Value = "SMART Cable" Then Sheets("Load v Distance").Visible = True Sheets("Load v Time").Visible = True Else Sheets("Load v Distance").Visible = False Sheets("Load v Time").Visible = False End If End If End Sub -- Regards, Tim Zych http://www.higherdata.com Workbook Compare - Excel data comparison utility "Al" wrote in message ... I have some code running in a worksheet (sheet 1) to hide two worksheets unless the value (text) in a cell (D4:E4 merged) of 'sheet 1' is a certain value. The value in 'D4:E4' is chosen from a list of 4 options (set up using a data validationlist). I am getting a type mismatch error if the value in 'd4' is deleted/cleared (not much of an issue as should only select form the list). More of an issue is I get the same error when any other merged cells (i.e. "C2:I2", "B18:J20") are cleared (i.e select cell and hit 'Delete'). Any ideas on why the error occurs or how to avoid it? The code: Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range("D4") Then If Target.Value = "SMART Cable" Then Sheets("Load v Distance").Visible = True Sheets("Load v Time").Visible = True Else Sheets("Load v Distance").Visible = False Sheets("Load v Time").Visible = False End If End If End Sub |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Type mismatch error - 'run-time error 13'
Patrick:
If you clear out D4, Target is $D$4:$E$4 due to the merged cell. Your macro has no condition to handle that. -- Regards, Tim Zych http://www.higherdata.com Workbook Compare - Excel data comparison utility "Patrick Molloy" wrote in message ... my test worked otherwise i wouldn't have posted it. If you step into the sub and ?target.Address it returns $D$4 Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$D$4" Then Select Case Target.Value Case "A" Sheet2.Visible = xlSheetHidden Sheet3.Visible = xlSheetHidden Case "B" Sheet2.Visible = xlSheetVisible Sheet3.Visible = xlSheetVisible End Select End If End Sub "keiji kounoike" <"kounoike AT mbh.nifty.com" wrote in message ... I don't think so. Tim has already explained the cause of error. I think your change couldn't solve the problem. Other than Tim's way, The code like If Target.Cells(1, 1) = Range("D4") Then If Target.Cells(1, 1) = "SMART Cable" Then might work. Keiji Patrick Molloy wrote: change If Target.Address = "$D$4:$E$4" Then to If Target.Address = "$D$4" Then Even though the two cells are merged, only the "active" cell, D4 is passed to the event handler "Al" wrote in message ... Hi Jacob, thanks for the quick reply however that code does not hide the sheets. "Jacob Skaria" wrote: Try with the below. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$D$4:$E$4" Then If Range("D4") = "SMART Cable" Then Sheets("Sheet2").Visible = True Sheets("Sheet3").Visible = True Else Sheets("Sheet2").Visible = False Sheets("Sheet3").Visible = False End If End If End Sub -- If this post helps click Yes --------------- Jacob Skaria "Al" wrote: I have some code running in a worksheet (sheet 1) to hide two worksheets unless the value (text) in a cell (D4:E4 merged) of 'sheet 1' is a certain value. The value in 'D4:E4' is chosen from a list of 4 options (set up using a data validationlist). I am getting a type mismatch error if the value in 'd4' is deleted/cleared (not much of an issue as should only select form the list). More of an issue is I get the same error when any other merged cells (i.e. "C2:I2", "B18:J20") are cleared (i.e select cell and hit 'Delete'). Any ideas on why the error occurs or how to avoid it? The code: Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range("D4") Then If Target.Value = "SMART Cable" Then Sheets("Load v Distance").Visible = True Sheets("Load v Time").Visible = True Else Sheets("Load v Distance").Visible = False Sheets("Load v Time").Visible = False End If End If End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Run-time error '13': Type mismatch | Excel Programming | |||
| Visual Basic Error Run Time Error, Type Mismatch | Excel Discussion (Misc queries) | |||
| Run-time error 13, Type Mismatch | Excel Programming | |||
| 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
