具有IF公式的列中的第一个空白（“”）单元格

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.

我想这个宏也可以转到C列的最后一个非空格单元格(或者第一个空格,我真的不在乎这两种方式)。我试过简单的结束/偏移代码,例如

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).

然而,我的问题是直接复制宏还复制了基础公式,对于C列,它是一个IF公式。因此,列中的单元格实际上不是空的,而是它们都具有IF公式,从而产生真/假值(分别为“”或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).

这意味着结束/偏移代码使用公式(C1000)转到列中的最后一个单元格,而不是转到具有值“”(当前为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?

我可以在此宏中添加什么代码来选择包含IF公式的第一个单元格,该公式导致值为“”----其外观为空白?

1 个解决方案

#1

After trying to be fancy with SpecialCells(), or using Find() or something I couldn't get it ...so here's a rather "dirty" way to do it:

尝试使用SpecialCells(),或使用Find()或其他东西后,我无法得到它...所以这是一个相当“肮脏”的方式来做到这一点:

Sub test()
Dim lastRow As Long, lastFormulaRow As Long
lastRow = Range("A" & Rows.Count).End(xlUp).Row

Dim i As Long
For i = lastRow To 1 Step -1
    If Cells(i, 1).Formula <> "" And Cells(i, 1).Value = "" Then
        lastFormulaRow = i
        Exit For
    End If
Next i

End Sub

Edit2: Here's one using .SpecialCells(). Granted I think we can whittle this down more, I like it better:

Edit2:这是一个使用.SpecialCells()。当然,我认为我们可以减少更多,我更喜欢它:

Sub lastRow()
Dim tempLastRow As Long
tempLastRow = Range("C" & Rows.Count).End(xlUp).Row 

Dim lastRow As Range
Set lastRow = Columns(3).SpecialCells(xlCellTypeFormulas).Find(What:="", LookIn:=xlValues, LookAt:=xlWhole, searchdirection:=xlPrevious, after:=Range("C" & tempLastRow))
Debug.Print lastRow.Row
End Sub

It returns 10 as the row.

它返回10作为行。

Edit: Be sure to add the sheet references before Range() and Cells() to get the last row. Otherwise, it's going to look at your active sheet to get the info.

编辑:确保在Range()和Cells()之前添加工作表引用以获取最后一行。否则,它将查看您的活动工作表以获取信息。

#1