PDA

View Full Version : Run time error #13 while macro runs



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

shrivallabha
02-01-2012, 06:19 AM
On what line does it error out?

mdmackillop
02-01-2012, 06:23 AM
Use Option Explicit and Dim all your variables. You'll need to post your ColumnLetter function as well to allow your code to be tested.