Results 1 to 14 of 14
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    vba vlookup (2002)

    Please, I need help with this vlookup, converting to vba.

    Sub xxx0808()

    'Set this workbook, Rejections 2

    Set tw = ActiveWorkbook.Sheets("Rejections 2")

    'Set 0808, reset filter, find last data row, set and sort total range

    Set wx = Workbooks.Open(Filename:="C:UsersNathanDocuments08 08mispymnt0808.xls").Worksheets(1)
    wx.Rows("1:1").Select
    Selection.AutoFilter
    Selection.AutoFilter

    wy = wx.Cells(wx.Rows.Count, 7).End(xlUp).Row 'Last 0808 data row number
    Set wz = wx.Rows("2:" & wy)

    wz.Select
    Selection.Sort Key1:=Range("I2"), Order1:=xlAscending, Key2:=Range("B2"), Order2:=xlDescending, _
    Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortTextAsNumbers, DataOption1:=xlSortTextAsNumbers

    'Activate Rejections 2, find last source account row number, set range

    tw.Activate
    Range("L2").Select

    tr = tw.Range("K1").End(xlDown).Row
    Set tt = tw.Range("K2:K" & tr)

    For Each z In tt
    z.Offset(0, 1).Formula = "=VLOOKUP........ ???? The non vba version =VLOOKUP(K2,[mispymnt0808.xls]Sheet1!$I:$J,2,FALSE)"
    Next z

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: vba vlookup (2002)

    If you really want to set the formula in VBA, it's

    z.Offset(0, 1).Formula = "=VLOOKUP(K" & z.Row & ",[mispymnt0808.xls]Sheet1!$I:$J,2,FALSE)"

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: vba vlookup (2002)

    An easier way: replace the loop

    For Each z In tt
    z.Offset(0, 1).Formula = "=VLOOKUP........ ???? The non vba version =VLOOKUP(K2,[mispymnt0808.xls]Sheet1!$I:$J,2,FALSE)"
    Next z

    with

    tt.Offset(0, 1).Formula = "=VLOOKUP(K2,[mispymnt0808.xls]Sheet1!$I:$J,2,FALSE)"

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vba vlookup (2002)

    Hans, you are a diamond!
    <tt.Offset(0, 1).Formula = "=VLOOKUP(K2,[mispymnt0808.xls]Sheet1!$I:$J,2,FALSE)"
    works great, lots of questions though....
    Can "wx" be used instead of "[mispymnt0808.xls]Sheet1!"
    Is it possible to get the result of the formula instead of the actual formula?, as text?

    The formula will return 3 results, acc no, 0, and #n/a.

    Can I then:
    z.Offset(0, 2) If acc no, then "X"
    z.Offset(0, 2) If 0, then "Y" - and delete the 0 from Offset(0, 1)
    z.Offset(0, 2) If #n/a, then "Z" - and delete the #n/a from Offset(0, 1)

    Thanks!!

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: vba vlookup (2002)

    1) No, you cannot use a VBA variable such as wx in a cell formula. Cell formulas don't "know" about VBA variables.

    2) You could use

    For Each z In tt
    z.Offset(0, 1).Value = Application.VLookup(z.Value, wx.Range("I:J"), 2, False)
    If IsError(z.Offset(0, 1)) Then
    z.Offset(0, 2) = "Z"
    z.Offset(0, 1).ClearContents
    ElseIf z.Offset(0, 1) = 0 Then
    z.Offset(0, 2) = "Y"
    z.Offset(0, 1).ClearContents
    Else
    z.Offset(0, 2) = "X"
    End If
    Next z

  6. #6
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vba vlookup (2002)

    Cool!!, nearly there..
    The account numbers distort with .Value (but the rest is great!) .Text??
    wx.close does not work? How do I close?
    My sample is fast (but small). Will this work OK where the look up is e.g 1,000 rows looking up 50,000? I can't test til Wednesday.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: vba vlookup (2002)

    What do you mean by "distort"?

    wx is a worksheet. You can't close a worksheet. You should close the workbook instead.

    Looking up 1,000 values in a table with 50,000 rows will probably be slow.

  8. #8
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vba vlookup (2002)

    I fixed the distort by formatting the cells to text prior to the lookup.
    Sorted the close.
    I'll prey for the speed!!
    Thanks.
    Finally......

    tr = tw.Range("I1").End(xlDown).Row
    Set tt = tw.Range("O2:O" & tr)

    For Each z In tt
    If z = 0 Then
    z Interior.ColorIndex = 3
    ' ElseIf z (CONTAINS -) Then
    ' z Interior.ColorIndex = 44
    End If
    Next z

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: vba vlookup (2002)

    Is that an additional question?

  10. #10
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vba vlookup (2002)

    yes, please..

    For Each z In tt
    If z = 0 Then
    z.Interior.ColorIndex = 3 'This should turn the 0's red but is affecting the whole range.

    And I am unsure how to achieve the "contains -"

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: vba vlookup (2002)

    It would have helped if you had stated the question explicitly instead of assuming that we would interpret a piece of code as a question.
    Try
    <code>
    ElseIf InStr(z, "-") > 0 Then
    </code>
    InStr returns the position within the first argument (the value of z) where the second argument ("-") is found, or 0 if it is not found.

  12. #12
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vba vlookup (2002)

    My apologies for not being clear.

    For Each z In tt
    If z = 0 Then
    z.Interior.ColorIndex = 3
    ElseIf InStr(z, "-") > 0 Then
    z.Interior.ColorIndex = 44
    End If
    Next z

    This is good for the positives, negatives and 0's, but is incorrectly colouring the "blanks"??

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: vba vlookup (2002)

    I already told you when you asked about conditional formatting that you have to test for "" first:

    If z = "" Then
    ...
    ElseIf z = 0 Then
    ...

    You asked about "contains -" so I answered that. If you want to check for negative numbers, why not use the simpler

    ElseIf z < 0 Then

  14. #14
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vba vlookup (2002)

    Thanks Hans.
    They are not negative numbers, they are pulled from "pcommPS", and I have not yet been able to get them out as negative numbers.

Posting Permissions

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