从上到下滚动列，并用上面的单元格替换0 [英] Scroll through column from top to bottom and replace 0's with value from cell above

问题描述

我必须滚动一列（实际上是两个，但是如果我可以得到一个我可以管理）从上到下，并替换 0 的空格细胞值高于它。这是我迄今为止（它不工作）：

I have to scroll through a column (actually two, but if I can get one going I can manage) from top to bottom and replace the 0's or blanks with cell value above it. This is what I have so far (it doesn't work):

Sub ReplaceZeros()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

lr = Cells(Rows.Count, "A").End(xlUp).Row                                          
For i = lr To 2 Step 7000                                                             
    If Cells(i, "A").Value = 0 Then Cells(i, "A").Replace_
    What: 0, Replacement:= "cell(i-1,"A").value,_
    SearchOrder:=xlByColumns, MatchCase:=True

Next i


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

推荐答案

p>如此接近：

Sub ReplaceZeros()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim lr As Long
Dim i As Long
Dim ws As Worksheet
Set ws = ActiveSheet
With ws
    lr = .Cells(.Rows.Count, "A").End(xlUp).Row
    For i = 2 To lr
        If .Cells(i, "A").Value = 0 Then .Cells(i, "A").Value = .Cells(i - 1, "A").Value
    Next i
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

注意：你已经从底部开始上升了，这样做会很好，但是如果你连续两个'0'，那么第二个将保持为零，再加上$ code> step 7000 使循环每次跳过7000行。

Note: The loop you had started at the bottom and went up, this would be fine but if you had two '0' in a row the second would remain zero, plus the step 7000 made the loop skip 7000 rows at a time.

然后如果你想要的是上面的值，使用 Cells（i- 1，A）。值将直接返回单元格的值。

Then if all you want is the value above using Cells(i-1,"A").value will return the value of the cells directly above.

这篇关于从上到下滚动列，并用上面的单元格替换0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！