Error Type Mis Match Error 13
Help!!
I have a rather large spreadsheet with over 10,000 cells containing various data. The code below helped me extract any information from the main spreadsheet (sheet1) and drop it into a new spreadsheet (sheet2). It has worked fine in my office 2000 but when I transferred it to XP I got this error message saying type mismatch error 13. So I transferred it back to 2000 and got the same message which I though was odd. The code steps past the input ox then the error appears. Can anyone help me here. Public Sub transfer() 'created by GC. powerexcel.co.uk 18/08/2004 Dim lastrow As Long Dim lastcol As Long Dim ir As Long, ic As Long, rd As Long Dim sString As String Dim yournewsheet As String yournewsheet = "Sheet2" Worksheets("Sheet1").Activate sString = InputBox("ENTER YOUR VALUE: ANY ROW ON WHICH THIS VALUE IS FOUND WILL BE COPIED TO A NEW SHEET") If sString = "" Then MsgBox "No search criteria requested.", vbOKOnly + vbInformation, "Cancel is pressed." Exit Sub End If lastrow = ActiveSheet.UsedRange.Rows.Count lastcol = ActiveSheet.UsedRange.Columns.Count Application.ScreenUpdating = False For ir = lastrow To 1 Step -1 For ic = lastcol To 1 Step -1 If UCase(Cells(ir, ic).Value) = UCase(sString) Then Rows(ir).Copy Destination:=Sheets(yournewsheet).Range("A" & Rows.Count).End(xlUp).Offset(1, 0) Rows(ir).Delete Shift:=xlUp ir = ir - 1 ic = lastcol + 1 rd = rd + 1 End If Next ic Next ir Application.ScreenUpdating = True MsgBox "You have deleted: " & rd & " rows" End Sub Thanks Gordon. |
Error Type Mis Match Error 13
Gordon
Try declaring sString as Variant. HTH Otto "Gordon" wrote in message ... Help!! I have a rather large spreadsheet with over 10,000 cells containing various data. The code below helped me extract any information from the main spreadsheet (sheet1) and drop it into a new spreadsheet (sheet2). It has worked fine in my office 2000 but when I transferred it to XP I got this error message saying type mismatch error 13. So I transferred it back to 2000 and got the same message which I though was odd. The code steps past the input ox then the error appears. Can anyone help me here. Public Sub transfer() 'created by GC. powerexcel.co.uk 18/08/2004 Dim lastrow As Long Dim lastcol As Long Dim ir As Long, ic As Long, rd As Long Dim sString As String Dim yournewsheet As String yournewsheet = "Sheet2" Worksheets("Sheet1").Activate sString = InputBox("ENTER YOUR VALUE: ANY ROW ON WHICH THIS VALUE IS FOUND WILL BE COPIED TO A NEW SHEET") If sString = "" Then MsgBox "No search criteria requested.", vbOKOnly + vbInformation, "Cancel is pressed." Exit Sub End If lastrow = ActiveSheet.UsedRange.Rows.Count lastcol = ActiveSheet.UsedRange.Columns.Count Application.ScreenUpdating = False For ir = lastrow To 1 Step -1 For ic = lastcol To 1 Step -1 If UCase(Cells(ir, ic).Value) = UCase(sString) Then Rows(ir).Copy Destination:=Sheets(yournewsheet).Range("A" & Rows.Count).End(xlUp).Offset(1, 0) Rows(ir).Delete Shift:=xlUp ir = ir - 1 ic = lastcol + 1 rd = rd + 1 End If Next ic Next ir Application.ScreenUpdating = True MsgBox "You have deleted: " & rd & " rows" End Sub Thanks Gordon. |
All times are GMT +1. The time now is 01:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com