Results 1 to 6 of 6

Thread: VLookup in VBA

  1. #1
    Bronze Lounger
    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.

  2. #2
    Bronze Lounger
    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

  3. #3
    Plutonium Lounger
    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...

  4. #4
    Bronze Lounger
    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
    I need a text string, so tried this, but get error 424?

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

  5. #5
    Plutonium Lounger
    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 read-only, 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

  6. #6
    Bronze Lounger
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •