VBA For Each – 用上方数据填充空白单元格

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

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

VBA For Each – 用上方数据填充空白单元格

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

huangapple
  • 本文由 发表于 2023年4月11日 07:48:09
  • 转载请务必保留本文链接:https://go.coder-hub.com/75981512.html
匿名

发表评论

匿名网友

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

确定