CODEWITHSUNDEEP
excelvbavlookup
Martin Himmelboe
Martin Himmelboe

Reputation: 23

Why won't VLookup work on my defined range?

When I run the third to last line with the VLookup, I keep getting "Method 'Range' of object '_Global' failed". It seems to be the reference to the Range("InfoOmArt") that is failing. I have been stuck forever now.

'Writes array "Artskoder" in a new worksheet
Dim InfoOmArt As Range
Sheets.Add
ActiveSheet.Name = "InformationOmArter"
Range("A1").Value = "Artsforkortelse"
Range("B1").Value = "Artskode"
Set InfoOmArt = ActiveSheet.Range("A1:B54")
InfoOmArt.Value = Artskoder
Worksheets("InformationOmArter").Move after:=Worksheets("Standarder")
Worksheets("Standarder").Activate


'Looping through sorter
Dim p As Range, abbr As Variant, MyStr As String

For Each p In Range(Cells(1, 1), Cells(44, 2))
    abbr = Cells(p.Row, 1).Value
    Debug.Print abbr
    MyStr = Application.WorksheetFunction.VLookup(abbr, Range("InfoOmArt"), 2, False) 'Arg.1 as a string seems to only works if it searhes in the spreadsheat, not in an array
    Cells(p.Row, 4).Value = MyStr
Next p

Upvotes: 2

Views: 84

Answers (1)

JvdV
JvdV

Reputation: 75840

There are a few problems here I would suggest need changing:

"I keep getting "Method 'Range' of object '_Global' failed""

The problem to the above is that you have set a Range variable called InfoOmArt. You then go and use that as a NamedRange within a Range(..) statement. This is incorrect, since your variable is already a Range object on its own. So change:

Range("InfoOmArt") into just InfoOmArt.

"But shouldn't it work when I specify it with Worksheets("InformationOmArter").Range("A1:B54"). I tried to change it to InfoOmArt. Now it says something about not being able to use property VLookup in Worksheetsfunction?"

No it doesn't have to work perse. As long as your variable Abbr holds a value that can NOT be found within the Range object, it will error out on you (error 1004 this time).

Then you are going through a Range object like this:

For Each p In Range(Cells(1, 1), Cells(44, 2))
    abbr = Cells(p.Row, 1).Value

This basically does the same thing twice. Range(Cells(1, 1), Cells(44, 1)), does the loop just once. And just for future references, it may even be better to pull these values into an array to loop through memory and don't make any calls to worksheet cells (which is slower on large ranges).

You also make a lot of implicit sheet references. These references do not hold any explicit Worksheet parent and thus make calls to the implicit ActiveSheet.

And this is something you should learn to avoid. ActiveSheet nor Activate is hardly ever needed. Have a look at this older thread on SO to learn how to avoid this.

My last suggestion is to start using Range.Find instead.

Upvotes: 1

Related Questions