求助：VBA代码无法移除单元格中的.pdf后缀，请帮忙排查

Hey there! Let's break down why your code isn't working and fix it step by step. I've spotted three key issues that are preventing it from updating your cells correctly:

Key Issues in Your Original Code #

• You don't update the str variable in the loop: You only set str to the value of cell A1 once at the start. As your loop increments the row number, str never gets updated to the current row's value—so you're always working with the first cell's content.
• Unnecessary (and broken) Replace logic: Your Replace line is redundant and flawed. Left(str, Len(str) - 4) already gives you the string without the last 4 characters (the .pdf), so replacing .pdf with that truncated string doesn't make sense. You can skip Replace entirely here.
• You never write the modified value back to the cell: You change the str variable, but you don't save that change back to the worksheet. That's why you see the correct value in the MsgBox but nothing changes in your sheet.

Corrected Code #

Here's the fixed version of your code with explanations for each change:

Sub removepdfword()
    Dim str As String
    Dim row As Long
    row = 1
    
    Do While Sheet1.Range("A" & CStr(row)) <> ""
        ' Update str to the current row's cell value every loop iteration
        str = Sheet1.Range("A" & CStr(row))
        
        If Right(str, 4) = ".pdf" Then
            ' Directly truncate the last 4 characters (no need for Replace)
            str = Left(str, Len(str) - 4)
            ' Write the modified value back to the cell
            Sheet1.Range("A" & CStr(row)).Value = str
            ' MsgBox str ' Keep this for debugging if needed, or comment it out
        End If
        
        row = row + 1
    Loop
End Sub

A More Concise Alternative #

If you prefer a cleaner approach, you can loop directly through the non-empty cells in column A instead of tracking row numbers:

Sub removepdfword()
    Dim cell As Range
    ' Get the range of non-empty cells in column A (from A1 to the last filled row)
    For Each cell In Sheet1.Range("A1:A" & Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row)
        If Right(cell.Value, 4) = ".pdf" Then
            ' Option 1: Truncate the last 4 characters
            cell.Value = Left(cell.Value, Len(cell.Value) - 4)
            ' Option 2: Replace ".pdf" with empty string (works if ".pdf" only appears at the end)
            ' cell.Value = Replace(cell.Value, ".pdf", "")
        End If
    Next cell
End Sub

Either version will correctly remove the .pdf suffix from your cells. Test it out, and it should work as expected!

内容的提问来源于stack exchange，提问作者chan hong chon