Error applying validation list to cell based on a named range

[Ark68]

I am running into an error when I'm trying to apply a validation list to a cell based on the values of a named range (nr_crwlist).

Set crwlist = ws_fmatemp.Range("CJ29:CJ" & rw - 1)
crwlist = crwlist
ThisWorkbook.Names.Add Name:="nm_crwlist", RefersTo:=crwlist

For f = 9 To 636
    If ws_fmagui.Cells(f, 23).Interior.Color = RGB(221, 235, 247) Then
        ws_fmagui.Cells(f, 26).Validation.Add Type:=xlValidateList, Formula1:="=nm_crwlist"
    End If
Next f

The line in red breaks with an error: "Application-defined or object-defined error"

Thoughts?

 

[rlv01]

Perhaps this.

    Set crwlist = ws_fmatemp.Range("CJ29:CJ" & rw - 1)

    For f = 9 To 636
        If ws_fmagui.Cells(f, 23).Interior.Color = RGB(221, 235, 247) Then
            With ws_fmagui.Cells(f, 26).Validation
                .Delete
                .Add Type:=xlValidateList, Formula1:="=" & crwlist.Address
            End With
        End If
    Next f

 

[Ark68]

Thank you so much rlv01 for your help.

For f = 9 To 636
    If ws_fmagui.Cells(f, 23).Interior.Color = RGB(221, 235, 247) Then
        With ws_fmagui.Cells(f, 26).Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:="=" & crwlist.Address
        End With
    End If
 Next f

The line in red breaks with an "Object required" error.

 

[6StringJazzer]

The RefersTo argument takes a String, not a Range. Did you check after running your code to see if the name was created correctly?

Change it to:

ThisWorkbook.Names.Add Name:="nm_crwlist", RefersTo:="=" & crwlist.Address

Also, by the way, this line

crwlist = crwlist

has absolutely no effect.

The rest of your code is OK.

Edit: The solution from rlv01 will give you the correct data validation, but the name nm_crwlist will still not be created correctly.

 

[Ark68]

Hi Jeff,
Thank you. That resolved that error.
However, despite range ws_fmatemp.range("CJ29:CJ39") holding values, the validation list is empty. When I check name manager to see if the named range nm_crwlist was created properly, the value is "#NAME?" and Refers to is =crwlist.

Something still isn't right.

 

[mikerickson]

Try

Set crwlist = ws_fmatemp.Range("CJ29:CJ" & rw - 1)
crwlist = crwlist
crwlist.Name = "nm_crwlist"

 

[Ark68]

Thank you Mike! That corrected the "#NAME?" error and the reference in the name manager, but my validation list is still empty at the cell.

    Set crwlist = ws_fmatemp.Range("CJ29:CJ" & rw - 1)
    'ThisWorkbook.Names.Add Name:="nm_crwlist", RefersTo:="=crwlist"
    crwlist.Name = "nm_crwlist"
    
    For f = 9 To 636
        If ws_fmagui.Cells(f, 23).Interior.Color = RGB(221, 235, 247) Then
            With ws_fmagui.Cells(f, 26).Validation
                .Delete
                .Add Type:=xlValidateList, Formula1:="=" & crwlist.Address
            End With
        End If
    Next f

 

[mikerickson]

What is the value of rw when it fails?

 

[rlv01]

Thank you so much rlv01 for your help.

For f = 9 To 636
    If ws_fmagui.Cells(f, 23).Interior.Color = RGB(221, 235, 247) Then
        With ws_fmagui.Cells(f, 26).Validation
            .Delete
           [COLOR=rgb(184, 49, 47)] .Add Type:=xlValidateList, Formula1:="=" & crwlist.Address[/COLOR]
        End With
    End If
 Next f

The line in red breaks with an "Object required" error.

I tested the code before I posted it, and it works if crwlist is a valid range and contains your validation list. I suspect at some point, this line

Set crwlist = ws_fmatemp.Range("CJ29:CJ" & rw - 1)

produces an invalid range, which goes back to @mikerickson's question about what the value of rw is when it breaks.

 

[Fluff]

As the list is on a different sheet you need

.Add Type:=xlValidateList, Formula1:="=" & crwlist.Address(, , , True)