rajkumar
01-31-2012, 07:29 PM
Hi,
I am facing a crazy problem in Excel 2007 and 2010, but not in 2003.
I use the below code to do find and replace from an external file.
It throws me run time error #13 type mismatch if i use this macro workbook in 2007 or 2010 but in excel 2003 it works fine.
anything need to be modified in this code? please help
attached a sample here.
Const strpath = "C:\Data_Analysis\"
Const strName = "Reference Table.xls"
Sub PatchConvert()
Application.ScreenUpdating = False
Dim wbSource As Workbook
Dim wbTarget As Workbook
Dim wsTarget As Worksheet
Set wbTarget = ActiveWorkbook
Set wsTarget = ActiveSheet
Dim fnd As Variant
Dim myitems As Long
Set wbSource = Workbooks.Open(FileName:=(strpath & strName), Origin:=xlWindows)
Set wsTarget = wbSource.Worksheets("MIF BASE") '<<== CHANGE TO SUIT
myitems = wsTarget.Range("A" & Rows.Count).End(xlUp).Offset(-1).Row
ReDim fnd(myitems, 1)
For myloop = LBound(fnd) To UBound(fnd)
fnd(myloop, 0) = wsTarget.Range("A" & myloop + 2) '<<== CHANGE TO SUIT
fnd(myloop, 1) = wsTarget.Range("C" & myloop + 2) '<<== CHANGE TO SUIT
Next myloop
wbTarget.Activate
Range(ColumnLetter(Selection) & "1").FormulaR1C1 = "PATCH"
For myloop = LBound(fnd) To UBound(fnd)
Selection.Replace What:=fnd(myloop, 0), Replacement:=fnd(myloop, 1), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next myloop
Selection.EntireColumn.AutoFit
wbSource.Close
Set wsTarget = Nothing
Set wbTarget = Nothing
Set wbSource = Nothing
Set fnd = Nothing
Application.ScreenUpdating = True
End Sub
I am facing a crazy problem in Excel 2007 and 2010, but not in 2003.
I use the below code to do find and replace from an external file.
It throws me run time error #13 type mismatch if i use this macro workbook in 2007 or 2010 but in excel 2003 it works fine.
anything need to be modified in this code? please help
attached a sample here.
Const strpath = "C:\Data_Analysis\"
Const strName = "Reference Table.xls"
Sub PatchConvert()
Application.ScreenUpdating = False
Dim wbSource As Workbook
Dim wbTarget As Workbook
Dim wsTarget As Worksheet
Set wbTarget = ActiveWorkbook
Set wsTarget = ActiveSheet
Dim fnd As Variant
Dim myitems As Long
Set wbSource = Workbooks.Open(FileName:=(strpath & strName), Origin:=xlWindows)
Set wsTarget = wbSource.Worksheets("MIF BASE") '<<== CHANGE TO SUIT
myitems = wsTarget.Range("A" & Rows.Count).End(xlUp).Offset(-1).Row
ReDim fnd(myitems, 1)
For myloop = LBound(fnd) To UBound(fnd)
fnd(myloop, 0) = wsTarget.Range("A" & myloop + 2) '<<== CHANGE TO SUIT
fnd(myloop, 1) = wsTarget.Range("C" & myloop + 2) '<<== CHANGE TO SUIT
Next myloop
wbTarget.Activate
Range(ColumnLetter(Selection) & "1").FormulaR1C1 = "PATCH"
For myloop = LBound(fnd) To UBound(fnd)
Selection.Replace What:=fnd(myloop, 0), Replacement:=fnd(myloop, 1), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next myloop
Selection.EntireColumn.AutoFit
wbSource.Close
Set wsTarget = Nothing
Set wbTarget = Nothing
Set wbSource = Nothing
Set fnd = Nothing
Application.ScreenUpdating = True
End Sub