recorded macro throwing 1004 error

[ursua]

Hi,
So i'm kinda new to macros so i am recording them to have them done and to learn something.
I have recorded a macro that puts a dropdown (this one is connected to the input of a cell in adjacent column).
The macro looks like this:

Sub Macro2()
'
' Macro2 Macro
'

'
    Range("AJ2").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:= _
        "=OFFSET(UseCases!$A$1,MATCH(AI2,UseCases!$A:$A,0)-1,1,COUNTIF(UseCases!$A:$A,AI2),1)"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

So in the column AJ it is making a dropdown (the source for it is in UseCases sheet) depending on an adjacent cell in column AI.
When trying to run this macro it throws run-time error '1004': Aplication-defined or object defined error here:

        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:= _
        "=OFFSET(UseCases!$A$1,MATCH(AI2,UseCases!$A:$A,0)-1,1,COUNTIF(UseCases!$A:$A,AI2),1)"

Can you please advise how to correct it?
Thanks,
ursua

 

[bobsan42]

I don't have your data so I can only guess.
The problem is with the range definition formula:
option 1: this COUNTIF(UseCases!$A:$A,AI2) is 0
option 2: this MATCH(AI2,UseCases!$A:$A,0) is 0
... or both

 

[felixstraube]

I think that the problem is that you have to add the sheets name in the formula like so

"=OFFSET(UseCases!$A$1,MATCH(Sheet1!AI2,UseCases!$A:$A,0)-1,1,COUNTIF(UseCases!$A:$A,Sheet1!AI2),1)"

I have the following code and it works ok:

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("B1").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=OFFSET(UseCases!$B$1, MATCH(Sheet1!$A1, UseCases!$A$1:$A$8, 0) - 1, , COUNTIF(UseCases!$A$1:$A$8, Sheet1!$A1))"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

 

[ursua]

so what i figured out is when i am starting macro in Sheet1 then everything is ok, when i am starting it in another sheet it doesn't know it.
i tried to change it

 Worksheets("Sheet1").Range("B1").Select

or

 Range("Sheet1!B1").Select

but that did not help.
i guess i just start my macro in proper sheet.
thanks.