Hi all,

Using Excel 2007

I'm trying to create a range object using SpecialCells(xlCellTypeBlanks) so I can then add a VLookup formula to each blank.

Excel is returning this error
Run-time error '1004':
The command you chose cannot be performed with multiple selections.
Select a single range and click the command again.
Is there a better way other than looping through each cell individually?
Will my Lookup formula update properly?
The formula below shows Row 2 but it should update based on the row it is pasted to.

thx
w

'Add lookup formulas to any blanks in the file     
     i = 0
     For i = LBound(sWBArray) To UBound(sWBArray)
        
        'Open Excel File
         Set wb= Workbooks.Open(sFldrOutput & sWBArray(i))
         With wb
            
            .Activate
            
           'Define range to filter
            Set ws= .Worksheets(1)
            With ws
                lRows = .Cells(Rows.Count, 1).End(xlUp).Row
                Set rng = .Range("E1:E" & lRows)
            End With
                      
           'Filter for blanks on Col E
            Set rngBlank = rng.SpecialCells(xlCellTypeBlanks).AutoFilter
            With rngBlank
                .Formula = "=vlookup(B2,tblData,4,False)"
                .Offset(0, 1).Formula = "=vlookup(B2,tblData,5,False)"
                .Offset(0, 2).Formula = "=vlookup(B2,tblData,6,False)"
                .Offset(0, 4).Formula = "=vlookup(B2,tblData,8,False)"
            End With
            
           'Save & close the Map Template
            .Save
            .Close
            
           'Destroy temorary objects
            Set rngBlank = Nothing
            Set rng = Nothing
            Set ws= Nothing
            Set wb= Nothing
         
         End With
         
     Next i