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
Bookmarks