代码之家 › 专栏 › 技术社区 › anky

写入单元格会写入整个范围

excel-vba vba

0

anky · 技术社区 · 7 年前

下面是我的代码:

Sub MyRenamePDF()

    Dim MyFolder As String
    Dim MyFile As String
    Dim i As Long
    Dim MyOldFile As String
    Dim MyNewFile As String
    Dim dt As String
    Dim FSO As Object
    Dim rng As Range
    Dim dat As Variant
    dt = Format(Now(), "YYYY_MM_DD_HH_MM")
    Set FSO = CreateObject("Scripting.Filesystemobject")
    MyFolder = "D:\test\"
    TargetFolder = "D:\output\"
    MyFile = Dir(MyFolder & "\*.pdf")

    Do While MyFile <> ""

        MyOldFile = MyFolder & "\" & MyFile
        MyNewFile = MyFolder & "\" & "0001" & "_" & dt & "_" & MyFile
        Name MyOldFile As MyNewFile

        With ThisWorkbook.Worksheets("Logs")
            Set rng = Range("B2:B100")
            For Each cell In rng
                If cell.Value = "" Then
                    cell.Value = MyNewFile
                End If
            Next cell
        End With
        FSO.MoveFile MyNewFile, TargetFolder
        MyFile = Dir
    Loop
End Sub

我在这个片段中遇到了一个问题:

With ThisWorkbook.Worksheets("Logs")
    Set rng = Range("B2:B100")
    For Each cell In rng
        If cell.Value = "" Then
            cell.Value = MyNewFile
        End If
    Next cell
End With

价值何在 MyNewFile 写入整个范围而不是特定的单元格,即 B2

怎么做?

1 回复 | 直到 7 年前

1

2

BigBen 7 年前

添加 Exit For If...End IF For 循环一次 MyNewFile

If cell.Value = "" Then
    cell.Value = MyNewFile
    Exit For
End If

Range.End 和 xlUp Offset .

With ThisWorkbook.Sheets("Logs")
    .Cells(.Rows.Count, "B").End(xlUp).Offset(1).Value = MyNewFile
End With