PDA

View Full Version : Why the errors? (Evaluate in Vlookup)



n8Mills
11-23-2008, 08:39 PM
Maybe somebody can pipe up on why this calculates just fine in the cell:

Sub test()

With Range("I1537:I1541")
.value = "=VLOOKUP(" & .Offset(0, -7).Address & ",'[wkbk1]Sheet1'!$" _
& ColumnLetter(3) & ":$" & ColumnLetter(10) & ",8,FALSE)"
End With

End Sub

Function ColumnLetter(Col)
'-----------------------------------------------------------------
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(Col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetter = sColumn
End Function
But when I try to use Evaluate it puts the first cell's value into the whole column

With Range("I1537:I1541")
.value = Evaluate("=VLOOKUP(" & .Offset(0, -7).Address & ",'[wkbk1]Sheet1'!$" _
& ColumnLetter(3) & ":$" & ColumnLetter(10) & ",8,FALSE)")
End With
WHYYYYYYYEEEEEEEEEEE?!?!

Thx,

n8

david000
11-24-2008, 12:20 AM
It's because you didn't allow the 'formulas' to do their thing; before they got converted into a values. Excel assumes you wanted the first value 'evaluated' in the entire range you specified.

It's like xld said, "Evaluate is not the panacea for all ails" so true!

There are lots of work-a-rounds though, but I for one would need a sample book to work on.

Bob Phillips
11-24-2008, 02:22 AM
I think that the reason is because the VLOOKUP does not return an array of values, it only returns a single value even when you use an array of values in the lookup argument. As it only returns a single value, it fills the whole range with that value.

When the expression being evaluated returns an array, that is populated cell by cell. It is the equivalents of



Range("M1:M10").Value = Application.Transpose(Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 0))


and



Range("N1:N10").Value = 1

n8Mills
11-25-2008, 12:01 AM
You fellas just like to say "panacea" dontchya? I'm gonna have to look that word up now.

Here's an example file I built so you can monkey with it, maybe tell me how your smarter than me.

http://www.freedrive.com/file/575513,testfile.xls (http://node1.nirvanix.com/lu5uZW1X%7EvpmsXOSqHp%7Er4N-sYsd%7EiDZYTXbyhV%7E19ra9OA/FreeDrive/fd_237179/Excel/testFile.xls?disposition=attachment)

Bob Phillips
11-25-2008, 01:27 AM
We just like to use appropriate terms, and panacea, a cure-all, is appropriate as you were trying to use it for everything.

But, what is your question, that file is completely empty as far as I can see.

david000
11-25-2008, 11:03 AM
There wasn't anything in your attachment FYI.

This is an alternative.


Sub vTest()
Dim r As Range
Dim cel As Range
Set r = Range("I1:I4")

For Each cel In r
cel = Application.WorksheetFunction.VLookup(cel.Offset(, -7), Sheet2.Range("C:J"), 8, 0)
Next
End Sub

n8Mills
11-25-2008, 01:10 PM
Boy, I'm some kind of goober. I didn't realize that I could upload a file here! Durrrrr. :cuckoo:

david000, I'm trying to do all of this without looping.

n8Mills
11-25-2008, 01:20 PM
BTW, I've tried to use "Evaluate" twice, among other solutions (not exactly trying to turn it into a panacea). I just used it in the subject header because it's compact and gets the point across. How else would you label this discussion?

Anyway, thanks for the word of the day. We'll see if Stephen Colbert tries to splice it into another $5 word for the sake of comedy.

Bob Phillips
11-25-2008, 01:23 PM
So what is the question?

You know you can't use Evaluate with VLOOKUP with multiple cells, and you know why.

n8Mills
11-25-2008, 06:57 PM
I want to write function values that typically calculated in-cell (ex: Vlookup) to a range of cells in a single step with an Autofilter applied.

I can currently do it in 4 steps: 1) Write formulas to cells, 2) Un-filter, 3) change formulas to values, 4) Re-filter.

I know it ain't broke, but I can still try to optimize it.

:bike:

david000
11-26-2008, 10:40 PM
I don't totally understand what you need, but this grabs both filtered or unfiltered lists then places them in a new sheet and finally throws an array entered Vlookup at 'em.

:turkey:


Sub Macro1()
Dim wExchDB As Worksheet, WIP As Worksheet, Temp As Worksheet
Dim r As Range, lr As Long, addr As String
Set WIP = Worksheets("WIP")
Set wExchDB = Worksheets("ExchDB")
Application.DisplayAlerts = False
Sheets("Temp").Delete
Application.DisplayAlerts = True
Set Temp = Worksheets.Add(Before:=Worksheets("WIP")): Temp.Name = "Temp"
With Temp
wExchDB.Cells(1, 1).CurrentRegion.SpecialCells(xlCellTypeVisible).Copy .Cells(1, 1)
WIP.Cells(1, 1).CurrentRegion.SpecialCells(xlCellTypeVisible).Copy .Cells(1, .Cells(1, .Columns.Count).End(xlToLeft).Column + 1)
lr = .Cells(.Rows.Count, 1).End(xlUp).Row
Set r = .Range("a2:a" & lr)
addr = r.Address
With r.Offset(, 1)
.FormulaArray = "=VLOOKUP(" & addr & "," & "D:E" & "," & 2 & "," & 0 & ")"
.Value = .Value
End With
.Columns("A:E").AutoFit
End With
End Sub

n8Mills
11-27-2008, 12:35 AM
Holy heck!

Right, so I'm starting to think that Autofilter is not something a coder would use, since it defies all attempts to work with it.

After spending all this time with you fine gentlemen I realize that I've been using Autofilter keep two sheets in one. This is further complicated by the fact that I use a second sheet for Vlookups, since the Autofilter doesn't hide rows from a Vlookup. So since I am using 2 sheets anyway, I'll use them the way Microsoft intended and forgo the use of Autofilters.

Thanks for helping me see the light, guys. I think things will be simpler this way.

Enjoy your Thanksgiving (http://blog.rifftrax.com/2008/11/24/happy-thanksbacon/),

Nate

http://farm1.static.flickr.com/43/108281845_efaf4c4daf.jpg