vba vlookup not working!!

[eHanSolo]

hi there,

trying to use the following but for some darn reason it's not working. I get no error messages and the stepping into it does nothing. Any ideas?

set myRng = Worksheets("Monthly").Range("A1:B10000")

correctVal = Workbooks("myWB.xls").Worksheets("Monthly").WorksheetFunction.VLookup(myVal, myRng, 2, False)

 

[AnotherHiggins]

You've already set the sheet when you defined myRng.

Replace the last line with this:

correctVal = WorksheetFunction.VLookup(myVal, myRng, 2, False)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

Actually, I would use the following:

correctVal = Application.WorksheetFunction.VLookup(myVal, myRng, 2, False)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[eHanSolo]

thanks for the replies.

It still doesn't work for me for some strange reason! so frustrating when you're stepping in to things and then nothing happens... no error msgs no nothing!

I've since found a workaround...but still not happy about letting this one go...

 

[AnotherHiggins]

What do you mean "nothing happens"?

It does step through, yes?

Does it just not return a value for correctVal?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[eHanSolo]

exactly! nothing is returned for correctVal.

pretty sure all my syntaxes are correct. really is 'eating' me up this one. grrr.

 

[AnotherHiggins]

If you just put a VLookup in the sheet, does it return a value?

Likely it is a problem where myVal is a number and the first column of myRng is text, or vice versa. Or maybe there are extra spaces in one or the other.

Just do it on the spreadsheet and see if you can find the difference.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[bpeirson]

I have had similar problems with VBA using excel. I have never solved the root problem, I have always found a work around because I don't have enough skill to work the actual problem.

I think what is happening is that VBA uses a different datatype (single, long, variant etc.) than Excel so when an exact match is required the same value stored in a different datatype is not really an exact match.

My problem arose when I had VBA enter data into a table then later check for the same data to avoid duplication. Even though VBA entered the data it would not recognize it later.

My work around was to transfer the cell contents into a VBA variable and then compare the variables in VBA to check for duplicate entries. This looping through the data has had an extreme effect on the speed of my code.

I would be inerested if someone has a quicker solution for this.