Results 1 to 14 of 14
Thread: vba vlookup (2002)

20081206, 12:06 #1
 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

20081206, 12:25 #2
 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)"

20081206, 12:33 #3
 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)"

20081206, 12:55 #4
 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!!

20081206, 13:04 #5
 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

20081206, 13:17 #6
 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.

20081206, 13:32 #7
 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.

20081206, 14:15 #8
 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

20081206, 22:05 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: vba vlookup (2002)
Is that an additional question?

20081206, 22:20 #10
 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 "

20081206, 22:33 #11
 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.

20081206, 22:45 #12
 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"??

20081206, 22:53 #13
 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

20081206, 23:08 #14
 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.