时间戳

首页 > 解决方案 > 如何从过滤的搜索中分配单元格值？

excel - 如何从过滤的搜索中分配单元格值？

问题描述

我试图在过滤条件中使用 do while 循环减去日期，但我不知道如何获取过滤行的单元格。

我尝试使用 .SpecialCells(xlCellTypeVisible)

Dim i As Integer
    i = 0
    k = 8
    Dim holder As Long
    ActiveSheet.Range("$G$9:$G$332").AutoFilter Field:=7, Criteria1:= _
        "AP"
    ActiveSheet.Range("$O$9:$O$332").AutoFilter Field:=15, Criteria1:= _
        "Released"

    Do While 79 > i


        i = i + 1
        k = k + 1
        ' ** Cells(k,"S") ** Is the problem
        holder = Cells(k, "S").SpecialCells(xlCellTypeVisible).Select

        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = "=RC[-6]-RC[-11]"


    Loop

我希望输出只会在过滤后的标准内减去。

标签： excelvba

解决方案

尝试这个：

Dim i As Integer

i = 0
k = 8

Dim holder As Long

ActiveSheet.Range("$A$9:$S$332").AutoFilter Field:=7, Criteria1:="AP"
ActiveSheet.Range("$A$9:$S$332").AutoFilter Field:=15, Criteria1:="Released"

Do While 79 > i


    i = i + 1
    k = k + 1

    If Not Intersect(ActiveSheet.Range("S" & k), ActiveSheet.Range("$A$9:$S$332").SpecialCells(xlCellTypeVisible)) Is Nothing Then

    ActiveSheet.Range("S" & k).FormulaR1C1 = "=RC[-6]-RC[-11]"

    End If

Loop

推荐阅读