Vlookup used in a loop not working after first 2 instanes

[ThrottleWorks]

I am facing a strange problem.


I have used vlookup in a loop, the loop is from 3 to 64.

while running the loop, the vlookup runs for row 3 & 4 only.


Then it is giving a bug, strange the same code was populating perfect results till yestarday. Nothing has changes but i am facing the bug.


The code I am using is as below.


Dim i as long

Range("A10000").select

selection.end(xlup).select


i = activecell.row


dim rng as range

set rng = range("B3:b"&i)


dim MyVal as intenger


For each rn in rng

if rn.value <> " " then

myval = rn.row

cells((rn.row,9).resize(1,1).select

activecell.value = application.worksheetfunction.vlookup(range("b" & (MyVal)).value, workbooks("masterdata").worksheet("data").range("e:cl"),86,0)

end if

next


The macro is giving results for 2 cells then I am getting error 1004,


Can anyone help me in this please.

 

[kaushik03]

Hi Sachin,


I believe this is the problem similar to your last post which was solved yesterday.


Could you plz upload your workbooks with the code in it?


Kaushik

 

[ThrottleWorks]

Yes Kaushik Sir, this is the same issue.


We cam not upload anything from office.

 

[NARAYANK991]

Hi Sachin ,


If you are getting an error at the VLOOKUP statement , then there is only one possibility , since you are executing the VLOOKUP statement only if the lookup value is non-blank ; the only possibility is if the value being looked up does not exist in the range E:CL.


Check the first three non-blank values in the range B3 downwards , and see if the corresponding values exist in column E in the tab labelled data.


Just incidentally , you can rewrite your code in a cleaner manner as follows :

[pre]

Public Sub temp()
Dim rng As Range, rng2 As Range
Dim i As Long, MyVal As Long

i = Range("A10000").End(xlUp).Row

Set rng = Range("B3:B" & i)
Set rng2 = Workbooks("Book2").Worksheets("data").Range("e:cl")

For Each rn In rng
If rn.Value <> "" Then
MyVal = rn.Row
Cells(MyVal, 9).Value = Application.WorksheetFunction.VLookup(rn, rng2, 86, 0)
End If
Next

Set rng = Nothing
Set rng2 = Nothing
End Sub

[/pre]
As far as possible avoid Activate , Select ; where possible , use variables to store intermediate results.


Narayan

 

[ThrottleWorks]

Hi Narayan Sir, good afternoon, sorry for the late reply.


Sir I will use the code given by you, will share the results.


Thanks a lot for the help & taking effort to write a code for me, have a nice day.

 

[ThrottleWorks]

Narayan Sir, this is working excellently, thanks a lot for the help.


I have a doubt, please reply if you get time, sir you set rng and rng2 as nothing at the end.


Since you have done it now I understand it is required, I never used to this.


Could you please tell me why do we have do write rng as nothing at the end.


Have a nice day.

 

[NARAYANK991]

Hi Sachin ,


It is not that it is absolutely required , but it is good programming practice.


http://dailydoseofexcel.com/archives/2004/06/07/nothing-keyword/


http://www.keysound.com/html/ch_15_much_ado_about_nothing.htm


Two contrary points of view ; take your pick !


Narayan

 

[ThrottleWorks]

Sir thanks a lot for the help & providing the reference for reading.


I have not read it yet, will do once I reach home, but will use the “good programming practice”.


It is always safe to follow a Ninja 