Run time error 13 while do a Excel v look up

[Giriraj Desai]

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)​

Giriraj Desai, 10 minutes ago​

 

[vijaySharma]

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)​

Giriraj Desai, 10 minutes ago

Try this Application.WorksheetFunction.VLookup

 

[Giriraj Desai]

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.

 

[vijaySharma]

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

 

[Giriraj Desai]

Hi Vijay,
The lookup 1 only is not getting picked up

Please find the file attached

 

[vijaySharma]

Giriraj,

Change

arg2 = myRange

to

Set arg2 = myRange

 

[Giriraj Desai]

Thank you very much vijay for your quick response,
and the file is working very fine.
You made my day.