Vlookup VBA error if value not found

[sriram170]

hi all,

i have two sheets which has thousand lines and i will update it daily so there will increase in line numbers day by day.. instead vlookup formula i planned to use worksheet function vlookup so that it can save time in calculating the worksheet for more than 10 mins. i tried with below code it was success but when the value is not found then it gives me a error "runtime error-1004" below is the vba code. is there any way we can run this without error pls help also

Sub test()
Dim x As Variant
Sheets("sheet1").Select
Range("h2").Select
Do
x = Application.WorksheetFunction.VLookup(ActiveCell.Offset(0, -7).Value, Worksheets("sheet2").Range("A:F"), 6, False)
If IsError(x) Then
ActiveCell.Value = "N\a"
Else


ActiveCell.Value = x
End If
ActiveCell.Offset(1, 0).Activate
Loop Until IsEmpty(ActiveCell.Offset(0, -7).Value)
End Sub

 

[Jonmo1]

Welcome to the board..

Remove the .WorksheetFunction part, and make it just Application.Vlookup

 

[sriram170]

thak you jonmo it worked like charm really i was suffering for this for past 3 days.. you are great.. thank you very much

Welcome to the board..Remove the .WorksheetFunction part, and make it just Application.Vlookup

 

[sriram170]

Re: Needed Macro for manual formulas

hi i need a macro for the below details.. i have been using formulas to get the details but it takes lot of time pls help...


I have a file with 4 columns filled with details as listed below

GL	GL	CC	CC	GL combination	CC combination	Input GL	Input cc	Yes/no
164001	164005	8000	8100
164006	164011	8101	8104
164012	164017	8106	8109
164018	164023	8111	8114
164024	164029	8116	8119
164030	164035	8121	8124

<tbody>
</tbody>

If I fill the INPUT GL column and INPUT CC column it should check with each combinations form gl and CC column, let me say if I give 164002 in gl and 8090 then it will check with each combinations from top to bottom if it finds in any row then it should result in yes.. please find a example given below

GL	GL	CC	CC	GL combination	CC combination	Input GL	Input cc	Yes/no
164001	164005	8000	8100	yes	yes	164002	8090	yes
164006	164011	8101	8104	no	no
164012	164017	8106	8109	no	no
164018	164023	8111	8114	no	no
164024	164029	8116	8119	no	no

<tbody>
</tbody>

If I have to check for nearly 1000 combinations then I have to copy and paste each combinations 1000 times and then have to find whether its available. Is there any macro that can find the combinations from the available data.


I Have another doubt how can i attacha a file so that u can refer to it i dont find any attach button

 

[stimon]

Thanks a lot Jonmo1! I also wasted 3 days to solve that and less than 1 minuto after reading your reply!

Thanks for helping us!

Welcome to the board..

Remove the .WorksheetFunction part, and make it just Application.Vlookup