I have an Excel sheet with locked cells. Some cells are unlocked to input values. There are many values to input therefore I'm trying to write a Macro that will: select the unlocked cells in the Active Sheet and fill the selected cells with a Vlookup formula that looks up the input values based on the first column and relevant rows in the same sheet from a table on a separate sheet. I have tried the below:
Sub SelectUnlockedCells()
'Update 20130830
Dim WorkRng As Range
Dim OutRng As Range
Dim Rng As Range
On Error Resume Next
Set WorkRng = Application.ActiveSheet.UsedRange
Application.ScreenUpdating = False
For Each Rng In WorkRng
If Rng.Locked = False Then
If OutRng.Count = 0 Then
Set OutRng = Rng
Else
Set OutRng = Union(OutRng, Rng)
End If
End If
Next
If OutRng.Count > 0 Then OutRng = Application.WorksheetFunction.VLookup("A" & ActiveRow.Value, Worksheets(2).Columns("A:C").Select, Worksheets(2).Columns(3).Select, False)
Application.ScreenUpdating = True
End Sub
I know my problem occurs in the last 4 lines in the Vlookup Worksheet Function, because if I say:
If OutRng.Count > 0 Then OutRng = 1 + 1
The unlocked input cells are imputed correctly as 2. Therefore I suspect my Vlookup object selection is not correct.
Any help would be great, thanks!
