paste to visible cells (excel 2007)

[jlax34]

Need some help finishing off this Macro. The portion of the code in question is below. It checks cell I1 (which subtotals the column), and if it comes up an error, it filtes the rows with the error and changes the vlookup to another file. The problem I have is with the code pasting the new vlookup to the other visible cells. I can see it correctly update the formula on the first visible row, but the copy down part doesnt work. I've tried searching but must not be using the magical words because most every response I find is how to COPY visible cells to another column/workbook etc....not copy a formula only to visible cells.

Note: I also have blank cells below this data so I need to set the range somehow to prevent copying tot he whole sheet

If IsError(Range("I1")) = True Then
    Range("I2").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$2:$N$150000").AutoFilter Field:=9, Criteria1:="#N/A"
    Range([H3], Cells(Rows.Count, "H")).SpecialCells(xlCellTypeVisible)(1).Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-6],'[Inventory View.xlsx]Sheet1'!C3:C22,20,FALSE)"
    Selection.FillDown
    ActiveSheet.ShowAllData
End If

A1	B	C	D	E	F	G	H	I
2								#N/A
3	Store	Item Code	Description	Bill Qty	Ship Pack	Cost	Cube	Total Cube
4	1	1	Misc	2	24	19.8	#N/A	#N/A
5	2	1	Misc	3	12	20.2	#N/A	#N/A
6

<tbody>
</tbody>

 

[JLGWhiz]

See if this will work.

If IsError(Range("I1")) = True Then
    Range("I2").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$2:$N$150000").AutoFilter Field:=9, Criteria1:="#N/A"
    Range([H3], Cells(Rows.Count, "H")).SpecialCells(xlCellTypeVisible)(1).FormulaR1C1 = _
        "=VLOOKUP(RC[-6],'[Inventory View.xlsx]Sheet1'!C3:C22,20,FALSE)"
    ActiveSheet.ShowAllData
End If

 

[jlax34]

Not quite. All that does is correctly select the first row of visible cells (after the 2 header rows) and update that row. It doesn't select any other rows that have an #N/A value in column H.

 

[jlax34]

Based on this I did find a way to get what I want, but I'm sure its a horrible way in the grand scheme of things. I can add a loop to the end of this section and update the top row each time until they are gone. I usually only have 4-5 lines that show up this way so it isn't too bad, but could be. I'd still like to find the "proper" way to do this so I know for future refrence.

If IsError(Range("I1")) = True Then
   Do Until IsError(Range("I1")) = False
    Range("I2").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$2:$N$150000").AutoFilter Field:=9, Criteria1:="#N/A"
    Range([H3:H150000], Cells(Rows.Count, "H")).SpecialCells(xlCellTypeVisible)(1).FormulaR1C1 = _
        "=VLOOKUP(RC[-6],'[Inventory View.xlsx]Sheet1'!C3:C22,20,FALSE)"
    ActiveSheet.ShowAllData
    Loop
End If

 

[JLGWhiz]

Not quite. All that does is correctly select the first row of visible cells (after the 2 header rows) and update that row. It doesn't select any other rows that have an #N/A value in column H.

The 'FillDown' statement requires that the source row be included in the range to be filled and the range must be greater than one row. If you used a specific range rather than 'Selection' lhen that statement should work. Provided you have the correct column reference, etc. Personally, I stay away from the select, activate and selection statements unless there are no other alternatives. Learning to use variables efrfectively was the best thing I ever did in using VBA.