VBA查找并替换Excel中的空单元格

huangapple go评论76阅读模式
英文:

VBA to Find And Replace Blank Cells in Excel

问题

有人关闭了我的问题,尽管我不知道它怎么能更加“集中”,它似乎是一个非常直接和简单的问题:

我试图编写一些VBA代码,以将工作表中一列中的空单元格替换为另一列中同一行的值。换句话说,遍历所有行,如果列 D 为空,就用列 A 中的值替换它。

我尝试过这个方法,但它只是用列A的顶行内容替换了空值:

.Columns("D").Replace What:="", Replacement:=.Columns("A"), LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

有什么建议吗?

谢谢!

英文:

Someone closed my question, although I don't know how it could be more "focused"; it seems like a pretty straightforward and simple question:

I'm trying to write some VBA code to replace blanks cells in one column of a worksheet with the value on the same row in another column. In other words, go down all the rows and if column D is blank, replace it with column A.

I tried this but it just replaces the blank value with the contents of the top row of Column A

.Columns("D").Replace What:="", Replacement:=.Columns("A"), LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Any suggestions?

Thanks!

答案1

得分: 1

需要循环处理此部分代码:

Dim lastRow as Long
lastRow = Range("D" & rows.count).End(xlUp).Row
Dim i as Long
For i = 2 to lastRow ' change 2 if you want to start at any other row
    If Cells(i,4).Value = "" Then Cells(i,4).Value = Cells(i,1).Value
Next i

希望这能帮助您。

英文:

You'd need to loop this:

Dim lastRow as Long
lastRow = Range("D" & rows.count).End(xlUp).Row
Dim i as Long
For i = 2 to lastRow ' change 2 if you want to start at any other row
    If Cells(i,4).Value = "" Then Cells(i,4).Value = Cells(i,1).Value
Next i

huangapple
  • 本文由 发表于 2020年1月3日 23:04:27
  • 转载请务必保留本文链接:https://go.coder-hub.com/59580860.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定