2

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
braX
  • 11,506
  • 5
  • 20
  • 33
Martin Himmelboe
  • 23
  • 2
  • To troubleshoot, I have also tried to just specify the range manually as Worksheets("InformationOmArter").Range("A1:B54") but it still fails. – Martin Himmelboe Jan 24 '20 at 08:21
  • `InfoOmArt` has been `Set` as a `Range` object. You can't call it as a `NamedRange`. So at least change `Range("InfoOmArt")` to `InfoOmArt ` – JvdV Jan 24 '20 at 08:27
  • 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? – Martin Himmelboe Jan 24 '20 at 08:29
  • It would also simply error out if your value can't be found, so also check if you got the correct data types, that you won't have any leading/trailing spaces or some sort of character throwing off your `Vlookup`. I also would advise to just go with `Range.Find` – JvdV Jan 24 '20 at 08:48
  • First of all. Thank you for taking you time to help me. I am still very new i the world of coding, and vba is the only one I have tried so far. I'm will try finding answers to your questions. The Abbr holds values of a three to four-letter combination.... – Martin Himmelboe Jan 24 '20 at 08:53
  • I think you hit the nail on the head. If it doesn't find value of abbr in the range it errors out. I found out the the first value of abbr is not the the specified range. Now I just need to figure out a way to handle that. Thank you!. Also I will have a look into the whole Activate and implicit sheet references you talked about :) – Martin Himmelboe Jan 24 '20 at 08:54
  • No wories, I don't know any other than `VBA` myself. To simplify your problem: The value that `Abbr` is holding **can not** be found using `Vlookup` in `InfoOmArt` – JvdV Jan 24 '20 at 08:56

1 Answers1

1

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.

JvdV
  • 70,606
  • 8
  • 39
  • 70