• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Run time error 13 while do a Excel v look up

Hi

I have created a macros which takes the information from the SAP and downloads into excel and formats ,recently we have upgraded from 2003 to 2007 .

The file was working fine in 2003 and in 2007 I am getting an error when i run the file

Could you please help me and the syntax is as below.

Sub CalcDiffs()
Dim lookup1, lookup2
Dim x1, x2, Diff
Dim arg1, arg2, arg3, arg4
Dim myRange As Range
Dim AcctVal1, AcctVal2
Dim DiffVal
Dim LastCalcRowIndex
Set myRange = Worksheets("Command Sheet").Range("A8:B19")

ActiveSheet.Range("A3").Select
Do While IsNumeric(ActiveCell.Value) = True

arg1 = ActiveCell.Value
AcctVal1 = ActiveCell.Offset(0, 3).Value
Set myRange = Worksheets("Command Sheet").Range("A8:B19")
arg2 = myRange
lookup1 = Application.VLookup(arg1, arg2, 2, False)
Set myRange = ActiveSheet.Range("F:I")
arg2 = myRange


lookup2 = Application.VLookup(lookup1, arg2, 4, False)
If IsError(lookup2) = False Then
AcctVal2 = lookup2
DiffVal = AcctVal1 + AcctVal2
End If
ActiveCell.Offset(0, 4).Value = DiffVal
ActiveCell.Offset(1, 0).Select
Loop
LastCalcRowIndex = ActiveCell.Row - 1
Do Until ActiveCell.Value = "Total"
ActiveCell.Offset(1, 0).Select
Loop
ActiveSheet.Range("E" & ActiveCell.Row).Formula = "=Sum(E3:E" & LastCalcRowIndex & ")"
Dim TotalDiffVal
TotalDiffVal = ActiveSheet.Range("E" & ActiveCell.Row).Value
If TotalDiffVal < 0 Then TotalDiffVal = TotalDiffVal * -1
If TotalDiffVal > 50000 Then
ActiveSheet.Tab.ColorIndex = 3
Else
ActiveSheet.Tab.ColorIndex = 43
End If

I am getting an error at this line where I am getting run time error no 13 Type mismatch

lookup2 = Application.VLookup(lookup1, arg2, 4, False)
 
Hi Vijay,

Thanks for reply I have tried replacing
arg1 = ActiveCell.Value
AcctVal1 = ActiveCell.Offset(0, 3).Value
Set myRange = Worksheets("Command Sheet").Range("A8:B19")
arg2 = myRange
lookup1 = Application.WorksheetFunction.VLookup(arg1, arg2, 2, False)
Set myRange = ActiveSheet.Range("F:I")
arg2 = myRange


lookup2 = Application.WorksheetFunction.VLookup(lookup1, arg2, 4, False)

Still I am getting the same error.
 
is lookup1 getting a value or not... check this by putting a break-point on the line.

Else try to upload your file for review.

Thanks
 
Back
Top