英文:
VBA For Each - Populate Blank Cells with Data Above
问题
标签名为"Stack"(未显示)。我希望根据上面的值填充空白。
下面的代码不会生成错误,但不会在空白单元格中填充任何内容。我用表格公式确认了""符合预期(例如,=A4=""返回TRUE)。
英文:
The tab is named "Stack" (not pictured). I am looking to populate blanks based on the value above with intermittent changes among the blanks.
The below code generates no errors, but does not populate anything in the blank cells. I confirmed with sheet formulas that "" is TRUE as expected (e.g. =A4="" returns TRUE).
Sub populapotato()
Dim myRange As Range
Dim potato As Variant
Set myRange = ActiveWorkbook.Sheets("Stack").Range("A4:A50")
For Each potato In myRange
'End loop at blank data cell
If potato.Offset(0, 1).Value = "" Then
Exit For
End If
'Populate File Name col if blank
If potato = "" Then
potato = potato.Offset(-1, 0).Value
Else
End If
Next potato
End Sub
答案1
得分: 2
使用 Range
作为 potato
的类型,并在分配值给单元格/区域时使用 .Value
:
英文:
Use Range
as the type for potato
, and use .Value
when assigning values to cells/ranges:
Sub populapotato()
Dim myRange As Range, potato As Range
Set myRange = ActiveWorkbook.Sheets("Stack").Range("A4:A16")
For Each potato In myRange.Cells
Debug.Print potato.Address, potato.Value = ""
If Len(potato.Offset(0, 1).Value) = 0 Then Exit For
If Len(potato.Value) = 0 Then potato.Value = potato.Offset(-1, 0).Value
Next potato
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论