Q:

VBA many Vlookups on filtered cells

I've been banging my head on this one for a while.
I have a big macro I made doing a number of operations on  a file but got stuck on doing a series of filtering and vlookups.
Here is a portion of what I got. I added comments to make it clearer.
 'FILTER ALL 3P VALUES IN ONE COLUMN AND ADD A VALUE IN ALL RESPECTIVE CELLS IN OTHER COLUMN
        Application.ScreenUpdating = False
      With ActiveSheet.UsedRange
        .AutoFilter Field:=22, Criteria1:="*3P*"
        .Offset(1).Range("AU1:AU" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Select

 'HERE I SELECT ALL VISIBLE FILTERED CELLS BY COUNTING IN ROW A BECAUSE THESE CELLS ARENT BLANK
        .Selection.Value = "3P PROGRAM"
        .AutoFilter
      End With

 'NOW I WANT TO FILTER ROW FOR BLANKS AND THEN FILL THIS RANGE WITH A FORMULA 
 'HERE IS THE PROBLEM

      With ActiveSheet.UsedRange
        .AutoFilter Field:=47, Criteria1:="="
        .Offset(1).Range("AU1:AU" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Select
        .Selection.FormulaR1C1 = "=VLOOKUP(RC[-38],'[WeeklyData.xlsx]Sheet1'!C8:C16,9,FALSE)"
        .AutoFilter
      End With

Problem is at the vlookup step. I want that range of visible filtered blank cells to get the value gotten by vlookup. Every cell should take a cell 38 columns to the left as a vlookup reference. 
I cant find a way to make formula work. I would like to: 
-insert vlookup to that filtered range, 
-remove filter (Autofilter)
-Select calculated column with offset of 1 for headers and paste as special values
- carry on to do this process 5, 6 more times for blank or invalid entries in other columns. 
Is there a way to do this?
Any help is appreciated

A:

I prefer the Range.CurrentRegion property over the Worksheet.UsedRange property. It refers to the 'data island' created at the origin point (in this case, A1).
With ActiveSheet
    If .AutoFilterMode Then AutoFilterMode = False
    With .Cells(1, 1).CurrentRegion
        'Set the filter
        .AutoFilter Field:=22, Criteria1:="*3P*"
        'Shift off the header row
        With .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0)
            'check if there are any visible cells
            If CBool(Application.Subtotal(103, .Cells)) Then
                'Put 3P PROGRAM into the visible cells in column AU
                Intersect(.Columns(47), .SpecialCells(xlCellTypeVisible)) = "3P PROGRAM"
            End If
        End With
        'remove the filter
        .AutoFilter Field:=22

        'set the formula on column AU blank cells
        Intersect(.Columns(47), .SpecialCells(xlCellTypeBlanks)).FormulaR1C1 = _
          "=VLOOKUP(RC[-38], '[WeeklyData.xlsx]Sheet1'!C8:C16, 9, FALSE)"

        'revert column AU within the .CurrentRegion to the values returned by the formulas
        .columns(47).cells = .columns(47).cells.value

    End With
End With

The second filter is replaced by using the Range.SpecialCells method with the xlCellTypeBlanks property. The Intersect method isolates the cell range reference to the blank cells within column AU. You may want to make a check for blank cells before running that operation.

