0

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?

Jakob
  • 4,784
  • 8
  • 53
  • 79

1 Answers1

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

Community
  • 1
  • 1
Ben Black
  • 3,751
  • 2
  • 25
  • 43
  • What if my str is not an object, but a string? – Jakob May 19 '14 at 14:05
  • It will still work; the `.ListFillRange` will accept a string argument (the string has to be properly formatted of course, your formula should work fine). If you look at the link I posted in the question it will have an example of this. – Ben Black May 19 '14 at 14:06
  • 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 – Jakob May 23 '14 at 08:25