Excel VBA：满足条件时如何给目标工作表E列原有文本前加指定内容？

Let's break down what's going wrong and fix it step by step:

The Root Issue #

In your code, you're setting cell = .Cells(i, 4) which points to column D of the source worksheet (Stig Jan). When you run:

wsTarget.Range("E" & iRowTarget).Value = "LAURA. " & cell.Value

You're concatenating "LAURA. " with the source column D value, not the content that's supposed to be in target column E (which comes from source column E). That's why you're seeing unexpected results.

Corrected Code #

Here's the fixed version with explanations of key changes:

Private Sub CommandButton1_Click()
    ' Fix variable declarations: explicitly type all variables to avoid unexpected behavior
    Dim wsSource As Worksheet, wsTarget As Worksheet
    Dim i As Long, iLastSource As Long, iRowTarget As Long, count As Long
    Dim cell As Range
    
    Set wsSource = Worksheets("Stig Jan")
    iLastSource = wsSource.Cells(Rows.Count, 1).End(xlUp).Row
    Set wsTarget = Worksheets("Laura Jan")
    count = 0
    
    With wsSource
        iRowTarget = wsTarget.Cells(Rows.Count, 1).End(xlUp).Row + 1
        For i = 36 To iLastSource
            Set cell = .Cells(i, 4)
            If cell.Font.Bold = False Then
                If cell.Value = "Fælles" Or cell.Value = "Lagt Ud" Then
                    ' Copy full row range from source to target
                    wsTarget.Range("A" & iRowTarget & ":H" & iRowTarget).Value = .Range("A" & i & ":H" & i).Value
                    wsTarget.Range("D" & iRowTarget).ClearContents
                    
                    ' Fix: Prepend "LAURA. " to the source column E value (which is now in target E)
                    wsTarget.Range("E" & iRowTarget).Value = "LAURA. " & .Range("E" & i).Value
                    
                    iRowTarget = iRowTarget + 1
                    count = count + 1
                End If
            End If
            If cell.Value = "Fælles" Or cell.Value = "Lagt Ud" Then
                wsSource.Rows(i).Columns("D").Font.Bold = True
            End If
        Next i
    End With
End Sub

Key Changes Made #

1. Fixed Variable Typing:
   • Original code only typed the last variable in each Dim line (e.g., wsSource was a Variant, not a Worksheet). Explicitly typing all variables prevents subtle bugs from untyped variants.
2. Target Column E Correction:
   • Replaced cell.Value (source D column) with .Range("E" & i).Value (source E column), which is the content that gets copied to target E column. Now "LAURA. " is prepended to the correct text as you intended.

内容的提问来源于stack exchange，提问作者Strune