VBA: Define variables in a range instead of the cells names

Question

I have a cell with the name "Choice Name" that is placed randomly in a lot of sheets of the same workbook. Please i would like to know how to look for that cell value in all sheets of the workbook.I tried a loop through all sheets and all cells of the first line looking for that String value but i can't seem to find a solution, this is what i did:

For Each ws In ActiveWorkbook.Worksheets 
    col_num = ws.Range("A1").End(xlToRight).Column 
       For Cel1 = 1 To clom_num 
         If ws.Cells(1, Cel1).Value = "Cell Name" Then 
         col_name = ws.Cells(2, Cel1).Name 
         End If 
       Next 
         With ws.Range("col_name:col_name").Validation   ' Here when i put ("L:L") it works, but that value can be in any cell
           .Delete 
           .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ 
    xlBetween, Formula1:=Liste 
           .IgnoreBlank = True 
           .InCellDropdown = True 
         End With

Oh i do get this error on the line (With ws.Range("col_name:col_name").Validation) "method range of object _worksheet failed"

Help please

tigeravatar tigeravatar · Accepted Answer · 2014-07-16T23:21:58

Instead of loops, use the Range.Find method:

    Dim ws As Worksheet
    Dim rngFound As Range

    For Each ws In ActiveWorkbook.Sheets
        Set rngFound = ws.Rows(1).Find("Cell Name", , xlValues, xlWhole)
        If Not rngFound Is Nothing Then
            With ws.Columns(rngFound.Column).Validation
                .Delete
                .Add xlValidateList, xlValidAlertStop, xlBetween, Liste
                .IgnoreBlank = True
                .InCellDropdown = True
            End With
        End If
    Next ws

VBA: Define variables in a range instead of the cells names

1 Answers