First blank ("") cell in column with IF formula

Viewed 741

I have a macro that exactly copies one sheet's data into another.

Sub QuickViewRegMgmt()

    ("Reg Management").Select
    Cells.Select
    Selection.Copy
    Sheets("Quick View Reg Mgmt").Select
    Cells.Select
    ActiveSheet.Paste

End Sub

I would like for this macro to also go to the last non-blank cell in Column C (or first blank, I really don't care either way). I tried simple end/offset code, e.g.

Range("A1").End(xldown).Offset(1,0).Select 

My problem, however, is that the direct copy macro also copies the underlying formulas, which for Column C is an IF formula. Therefore, no cell in the column is actually empty, but rather they all have an IF formula resulting in a true/false value (respectively, a "" or VLOOKUP).

=IF(VLOOKUP('Reg Management'!$Y260,'Reg Guidance'!$A:$V,3,FALSE)=0,"",VLOOKUP('Reg Management'!$Y260,'Reg Guidance'!$A:$V,3,FALSE))

That means the end/offset code goes to the last cell in the column with the formula (C1000) instead of going to the first cell that has a value of "" (which is currently C260).

What code can I add to this macro to select the first cell that contains an IF formula resulting in a value of "" ---- which has the appearance of being blank?

1 Answers
Related