method range of object _worksheet failed

Question

I have a formula that I'm trying to plot in to a range - through Print.Debug I can see that my Formula makes sense, but the Range doesn't compute, but throws the error headlined.

In the Print.Debug I can see that str is =

=OFFSET(Menu!A:A,(3+COUNTIF(Menu!F:F,"Drikke")+COUNTIF(Menu!F:F,"Ernæring")-1),0,COUNTIF(Menu!F:F,"Mad"),1)

My line of code looks like:

With cboTemp
  'show the combobox with the list
  .Visible = True
  .Left = Target.Left
  .Top = Target.Top
  .Width = Target.Width + 5
  .Height = Target.Height + 5
  Problem is, that I'm taking the formula from a datavalidated, and it's not parsing correctly. I'm using: .ListFillRange = Target.Validation.Formula1 , but the range of the datavalidation is not shown, and I know from the original cell, that the formula is valid <---- Line that throws error
  .LinkedCell = Target.Address
End With

Why is the error being thrown?

EDIT

I've tried taking the formula (str) from a datavalidated cell, using: .ListFillRange = Target.Validation.Formula1 , but the range of the datavalidation is not shown, and I know from the original cell, that the formula is valid

I now don't get an error, but also no range is shown - I suspect that this has something to do with the "IgnoreBlankCells" property?

Answer 1

Instead of giving it a Range object, just give it your str object.

Try

.ListFillRange = str

Here's a good question on the subject of setting this dynamically