Results 1 to 6 of 6
Thread: VLookup in VBA

20090605, 20:28 #1
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
Anyone see my problem here? I'm getting #Name.
x8 = w8.Cells(w8.Rows.Count, 4).End(xlUp).Row
Set y8 = w8.Range("E2:E" & x8)
For Each RngFind In y8
RngFind.Value = "=VLOOKUP(LEFT(RngFind.Offset(0, 1),6),Bins,3)"
RngFind.Offset(0, 1).Formula = "=RngFind=Rngfind.Offset(0, 4)"
Next
In E, I am trying to look up the left 6 of D
In F, I want to know if E matches A.

20090605, 20:58 #2
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
Think I'm getting closer, but still no cigar.....
Code:Dim Bins As Range x8 = w8.Cells(w8.Rows.Count, 4).End(xlUp).Row Set y8 = w8.Range("E2:E" & x8) For Each RngFind In y8 RngFind.Value = Application.VLookup(Left(RngFind.Offset(0, 1), 6), Bins, 3, 0) ' Next

20090606, 06:58 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
You declare a variable Bins but you don't set it in your code, so it's undefined when you get to VLookup...

20090606, 08:15 #4
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
Aha, I had defined the range inside excel, and assumed that would carry, clearly not. So this works, but outputs as a value.
Code:Dim Bins As Range Set Bins = xx.Worksheets("Bins").Range("A1:C46") x8 = w8.Cells(w8.Rows.Count, 4).End(xlUp).Row Set y8 = w8.Range("E2:E" & x8) For Each RngFind In y8 RngFind.Value = Application.VLookup(Left(RngFind.Offset(0, 1), 6), Bins, 3, 0) Next
RngFind.Text = Application.VLookup(Left(RngFind.Offset(0, 1), 6), Bins, 3, 0)
And how would I then go about deleting each row where RngFind is not equal to RngFind.Offset(0, 4) ?

20090606, 08:45 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Since you've been asking completely unrelated questions in the topic "Application.Workbooks.Count", I've split it into separate topics.
In the future, please ask related questions in the same topic, but unrelated questions in a new topic.
The Text property of a Range object is readonly, you can't assign a value to it. If the result of VLookup is a number but you want to store it as a text value, you can use
rngFind.Value = "'" & Application.VLookup(Left(rngFind.Offset(0, 1), 6), Bins, 3, 0)
The apostrophe in front of the value forces Excel to treat it as text.
If you want to delete rows, you must loop backwards:
Code:Dim r As Long x8 = w8.Cells(w8.Rows.Count, 4).End(xlUp).Row Set y8 = w8.Range("E2:E" & x8) For r = x8 To 2 Step 1 w8.Cells(r, 5) = "'" & Application.VLookup(Left(w8.Cells(r, 4), 6), Bins, 3, 0) If Not w8.Cells(r, 5) = w8.Cells(r, 1) Then w8.Cells(r, 5).EntireRow.Delete End If Next r

20090606, 11:53 #6
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
Thanks Hans, I will remember in future to keep my topics separate.
I eventually worked out the rngFind.Value = "'" & Application......... bit, Thanks for the loop!