VBA dropdown list error

[R2ah1ze1l]

    With wsReview.Range(Cells(9, 20), Cells(9, ic + lastr)).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _    <---Highlight in yellow
        Operator:=xlBetween, Formula1:="Pool"                             <---Arrow on this line
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

I am receiving a Run-time error '1004': Application-defined or object-defined error
I copied this from a recorded macro then edited the range selection.

 

[R2ah1ze1l]

No matter what I change these two lines are highlight yellow:

        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$A$1"

and there is an arrow pointing to this line:

        xlBetween, Formula1:="=$A$1"

Do I need to declare something?

 

[Gokhan Aycan]

Your code works, I would check if the range is what you want.

I would check the values for ic and lastr and the sum doesn't go below 1.

When you debug, hover mouse over ic and lastr, or just add a watch for (ic + lastr). You can also write ? ic, ? lastr or ? ic + lastr etc in the Immediate window

 

[R2ah1ze1l]

Your code works, I would check if the range is what you want.

I would check the values for ic and lastr and the sum doesn't go below 1.

When you debug, hover mouse over ic and lastr, or just a watch ic + lastr

ic = 19
lastr = 109

for this example (this was with a mouse hover over)

 

[Gokhan Aycan]

Just changed first line here: With ActiveSheet.Range(Cells(1, 4), Cells(2, 5)).Validation

Any protection on the sheet maybe?

Also, just an observation, lastr as in LastRow? So on row 9, from columns 20 to 128, is that what you want?

 

[R2ah1ze1l]

I have not protected any cells/worksheets/workbooks, manually or via vba.
In this instance, yes I want the list box applied along row 9 from column 20 to 128.

Every time I try the error flags the formula1 line...

 

[Gokhan Aycan]

Can you try writing that line in 1 line?
As in:

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Pool"

 

[Gokhan Aycan]

Is this sheet the active sheet?

 

[R2ah1ze1l]

Still results in 1004 error: Application-defined or object-defined error
And as one line it is all highlight with an arrow pointed to it.

Yes, this is the active sheet.

 

[Gokhan Aycan]

Is the code an a standard module or a sheet module? Can't think of anything else other than use fully qualified cell references as well.

With wsReview.Range(wsReview.Cells(9, 20), wsReview.Cells(9, ic + lastr)).Validation