excel - 如何使用 VBA 自动将单词添加到多列中的文本末尾？

Question

我需要在两列中的每个条目的末尾添加一个 .mp3。我有下面的代码，但我必须选择列中的每个项目并将其应用于这些单元格。
但我希望有一个代码可以自动将 .mp3 添加到 B 列和 D 列中任何条目的末尾。
这是我当前的代码:

Sub AppendToExistingOnRight()
Dim c as range
For each c in Selection
  If c.value <> "" Then c.value = c.value & ".mp3”
Next
End Sub

任何帮助将不胜感激，以帮助提高效率。

Answer 1

解决方案没有 循环。我正在为列 B 显示它.随意将其改编为专栏 D

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range
    Dim sAddr As String
    Dim lRow As Long
    
    '~~> Change sheet name as applicable
    Set ws = Sheet1
    
    With ws
        '~~> Find last row in Col B
        lRow = .Range("B" & .Rows.Count).End(xlUp).Row
        
        '~~> Construct your range
        Set rng = Range("B1:B" & lRow)
        
        sAddr = rng.Address
        
        '~~> Append ".mp3" to the entire range in 1 go
        rng = Evaluate("index(concatenate(" & sAddr & ","".mp3""),)")
    End With
End Sub

在行动

如果您想了解这是如何工作的，那么您可能想查看 Convert an entire range to uppercase without looping through all the cells