excel - 在范围(文本模板)中搜索字符串并从动态行替换

Question

目前我有一个模板，它在名为 rngP1 的范围内。 .

这包含以下文本:

"This is to confirm that strTitle has been enacted on strDate for strCompany."

基本上，我在另一张表中有一个数据，将用于从我的模板中替换这 3 个字符串:



所以我希望在每一行数据中搜索字符串 strTitle , strDate , 和 str公司并根据每一行的数据进行替换。

我已经有一个代码，但是它并没有像我预期的那样工作:

Sub example()
    Dim wsMain As Worksheet
    Set wsMain = Sheets("Main")
    Dim wsTemplate As Worksheet
    Set wsTemplate = Sheets("Template")
    Dim textToReplace As Variant
    Dim array_example()
    Dim Find_Text As Variant
    Dim str As String

    last_row = wsMain.Range("A1").End(xlDown).Row 'Last row of the data set

    ReDim array_example(last_row - 1, 2)

    Find_Text = Array("strTitle", "strDate", "strCompany")
    str = wsTemplate.Range("rngP1").Value
    'Storing values in the array
    For i = 0 To last_row - 1   
        array_example(i, 0) = wsMain.Range("A" & i + 2)
        array_example(i, 1) = wsMain.Range("C" & i + 2)
        array_example(i, 2) = wsMain.Range("D" & i + 2)    
    Next

    For i = LBound(array_example, 1) To UBound(array_example, 1)
        For j = LBound(array_example, 2) To UBound(array_example, 2)
            For a = 0 To UBound(Find_Text)       
                str = Replace(str, Find_Text(a), array_example(i, j))   
            Next a
        Next j

        MsgBox str
    Next i  
End Sub

错误的输出:



它应该是:

This is to confirm that Title1 has been enacted on 13-October-18 for Company X.

下一个将是下一行，即 title 2 .等等等等堡垒。

如果您有其他方法可以做到这一点，我将不胜感激。

Answer 1

这是一个工作示例:

您可以将工作表中的数据范围推送到一行中的数组而无需循环

DataArr = wsMain.Range("A2:D" & LastRow).Value

您只需要 2 个循环即可进行替换:

一个循环遍历数据行

一个循环遍历变量以替换

您的模板 str未在循环内初始化，但您需要为每个数据行创建一个新模板。

请注意，从范围加载的数组从 1 开始计数。但变量数组从 0 开始计数.

Option Explicit

Sub Example()
    Dim Template As String
    Template = "This is to confirm that strTitle has been enacted on strDate for strCompany."
    'load your template string from worksheet here!

    Dim Variables As Variant 'variables to be replaced
    Variables = Array("strTitle", "strDate", "strCompany")

    Dim wsMain As Worksheet
    Set wsMain = ThisWorkbook.Worksheets("Main")

    Dim LastRow As Long 'this method is more reliable to find the last used row
    LastRow = wsMain.Cells(wsMain.Rows.Count, "A").End(xlUp).Row

    Dim DataArr As Variant 'load the complete data range into an array
    DataArr = wsMain.Range("A2:D" & LastRow).Value

    Dim Output As String

    Dim iRow As Long, iVar As Long
    For iRow = LBound(DataArr, 1) To UBound(DataArr, 1) '1 to LastRow
        Output = Template 'initialize with the template!

        For iVar = LBound(Variables) To UBound(Variables) ' 0 to 2
            Output = Replace(Output, Variables(iVar), DataArr(iRow, iVar + 1))
        Next iVar

        Debug.Print Output
    Next iRow
End Sub