Mixing
Search string in a range (text template) and replace from dynamic rows
Currently I have a template which is in range called rngP1.
And this contains a text below:
"This is to confirm that strTitle has been enacted on strDate for strCompany."
Basically, I have a data in another sheet that will be used to replace these 3 strings from my template:
So what I would like to happen is that in every row data it will search strings strTitle, strDate, and strCompany and replace them according to the data of each row.
I have a code already, however, it doesn't work as I expected:
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
Wrong Output:
It should be:
This is to confirm that Title1 has been enacted on 13-October-18 for Company X.
And next one would be the next row which is title 2. So on and so fort.
If you have an alternative way to do it, I appreciate it.
excel vba excel-vba
edited Nov 22 '18 at 7:44
Pᴇʜ
23.4k62951
asked Nov 22 '18 at 7:33
SevpointSevpoint
89110
add a comment |
Currently I have a template which is in range called rngP1.
And this contains a text below:
"This is to confirm that strTitle has been enacted on strDate for strCompany."
Basically, I have a data in another sheet that will be used to replace these 3 strings from my template:
So what I would like to happen is that in every row data it will search strings strTitle, strDate, and strCompany and replace them according to the data of each row.
I have a code already, however, it doesn't work as I expected:
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
Wrong Output:
It should be:
This is to confirm that Title1 has been enacted on 13-October-18 for Company X.
And next one would be the next row which is title 2. So on and so fort.
If you have an alternative way to do it, I appreciate it.
excel vba excel-vba
edited Nov 22 '18 at 7:44
Pᴇʜ
23.4k62951
asked Nov 22 '18 at 7:33
SevpointSevpoint
89110
I didn't test it, but I guess you need to get rid ofFor a = 0 To UBound(Find_Text)loop and instead write three lines with replacing.
– JohnyL
Nov 22 '18 at 7:46
Str is defined as a string. You assign to it once outside of the loops. In the loops you then repeatedly do replaces on str. As str is a vba variable which is not linked to your worksheet this may be where you have your error.
– Freeflow
Nov 22 '18 at 8:01
add a comment |
Currently I have a template which is in range called rngP1.
And this contains a text below:
"This is to confirm that strTitle has been enacted on strDate for strCompany."
Basically, I have a data in another sheet that will be used to replace these 3 strings from my template:
So what I would like to happen is that in every row data it will search strings strTitle, strDate, and strCompany and replace them according to the data of each row.
I have a code already, however, it doesn't work as I expected:
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
Wrong Output:
It should be:
This is to confirm that Title1 has been enacted on 13-October-18 for Company X.
And next one would be the next row which is title 2. So on and so fort.
If you have an alternative way to do it, I appreciate it.
excel vba excel-vba
edited Nov 22 '18 at 7:44
Pᴇʜ
23.4k62951
asked Nov 22 '18 at 7:33
SevpointSevpoint
89110
Currently I have a template which is in range called rngP1.
And this contains a text below:
"This is to confirm that strTitle has been enacted on strDate for strCompany."
Basically, I have a data in another sheet that will be used to replace these 3 strings from my template:
So what I would like to happen is that in every row data it will search strings strTitle, strDate, and strCompany and replace them according to the data of each row.
I have a code already, however, it doesn't work as I expected:
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
Wrong Output:
It should be:
This is to confirm that Title1 has been enacted on 13-October-18 for Company X.
And next one would be the next row which is title 2. So on and so fort.
If you have an alternative way to do it, I appreciate it.
excel vba excel-vba
excel vba excel-vba
edited Nov 22 '18 at 7:44
Pᴇʜ
23.4k62951
asked Nov 22 '18 at 7:33
SevpointSevpoint
89110
edited Nov 22 '18 at 7:44
Pᴇʜ
23.4k62951
asked Nov 22 '18 at 7:33
SevpointSevpoint
89110
edited Nov 22 '18 at 7:44
Pᴇʜ
23.4k62951
edited Nov 22 '18 at 7:44
Pᴇʜ
23.4k62951
edited Nov 22 '18 at 7:44
Pᴇʜ
23.4k62951
23.4k62951
asked Nov 22 '18 at 7:33
SevpointSevpoint
89110
asked Nov 22 '18 at 7:33
SevpointSevpoint
89110
asked Nov 22 '18 at 7:33
SevpointSevpoint
89110
89110
I didn't test it, but I guess you need to get rid ofFor a = 0 To UBound(Find_Text)loop and instead write three lines with replacing.
– JohnyL
Nov 22 '18 at 7:46
Str is defined as a string. You assign to it once outside of the loops. In the loops you then repeatedly do replaces on str. As str is a vba variable which is not linked to your worksheet this may be where you have your error.
– Freeflow
Nov 22 '18 at 8:01
add a comment |
I didn't test it, but I guess you need to get rid ofFor a = 0 To UBound(Find_Text)loop and instead write three lines with replacing.
– JohnyL
Nov 22 '18 at 7:46
Str is defined as a string. You assign to it once outside of the loops. In the loops you then repeatedly do replaces on str. As str is a vba variable which is not linked to your worksheet this may be where you have your error.
– Freeflow
Nov 22 '18 at 8:01
I didn't test it, but I guess you need to get rid of
For a = 0 To UBound(Find_Text) loop and instead write three lines with replacing.– JohnyL
Nov 22 '18 at 7:46
I didn't test it, but I guess you need to get rid of
For a = 0 To UBound(Find_Text) loop and instead write three lines with replacing.– JohnyL
Nov 22 '18 at 7:46
Str is defined as a string. You assign to it once outside of the loops. In the loops you then repeatedly do replaces on str. As str is a vba variable which is not linked to your worksheet this may be where you have your error.
– Freeflow
Nov 22 '18 at 8:01
Str is defined as a string. You assign to it once outside of the loops. In the loops you then repeatedly do replaces on str. As str is a vba variable which is not linked to your worksheet this may be where you have your error.
– Freeflow
Nov 22 '18 at 8:01
add a comment |
1 Answer
1
active
oldest
votes
Here is a working example:
You can push the data range from a worksheet into an array with one line without looping
DataArr = wsMain.Range("A2:D" & LastRow).Value
You need only 2 loops for the replacing:
- one to loop through the data rows
- one to loop through the variables to replace
Your template
strwas not initialized within the loop, but you need a fresh template for every data row.
Note that the array loaded from the range starts counting from 1 but the variables array starts counting from 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
answered Nov 22 '18 at 8:00
PᴇʜPᴇʜ
23.4k62951
Thank you Peh, I have tried to code above. Also, thanks for explanation.
– Sevpoint
Nov 22 '18 at 8:16
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53425924%2fsearch-string-in-a-range-text-template-and-replace-from-dynamic-rows%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Here is a working example:
You can push the data range from a worksheet into an array with one line without looping
DataArr = wsMain.Range("A2:D" & LastRow).Value
You need only 2 loops for the replacing:
- one to loop through the data rows
- one to loop through the variables to replace
Your template
strwas not initialized within the loop, but you need a fresh template for every data row.
Note that the array loaded from the range starts counting from 1 but the variables array starts counting from 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
answered Nov 22 '18 at 8:00
PᴇʜPᴇʜ
23.4k62951
Thank you Peh, I have tried to code above. Also, thanks for explanation.
– Sevpoint
Nov 22 '18 at 8:16
add a comment |
Here is a working example:
You can push the data range from a worksheet into an array with one line without looping
DataArr = wsMain.Range("A2:D" & LastRow).Value
You need only 2 loops for the replacing:
- one to loop through the data rows
- one to loop through the variables to replace
Your template
strwas not initialized within the loop, but you need a fresh template for every data row.
Note that the array loaded from the range starts counting from 1 but the variables array starts counting from 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
answered Nov 22 '18 at 8:00
PᴇʜPᴇʜ
23.4k62951
Thank you Peh, I have tried to code above. Also, thanks for explanation.
– Sevpoint
Nov 22 '18 at 8:16
add a comment |
Here is a working example:
You can push the data range from a worksheet into an array with one line without looping
DataArr = wsMain.Range("A2:D" & LastRow).Value
You need only 2 loops for the replacing:
- one to loop through the data rows
- one to loop through the variables to replace
Your template
strwas not initialized within the loop, but you need a fresh template for every data row.
Note that the array loaded from the range starts counting from 1 but the variables array starts counting from 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
answered Nov 22 '18 at 8:00
PᴇʜPᴇʜ
23.4k62951
Here is a working example:
You can push the data range from a worksheet into an array with one line without looping
DataArr = wsMain.Range("A2:D" & LastRow).Value
You need only 2 loops for the replacing:
- one to loop through the data rows
- one to loop through the variables to replace
Your template
strwas not initialized within the loop, but you need a fresh template for every data row.
Note that the array loaded from the range starts counting from 1 but the variables array starts counting from 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
answered Nov 22 '18 at 8:00
PᴇʜPᴇʜ
23.4k62951
edited Nov 22 '18 at 8:11
answered Nov 22 '18 at 8:00
PᴇʜPᴇʜ
23.4k62951
answered Nov 22 '18 at 8:00
PᴇʜPᴇʜ
23.4k62951
answered Nov 22 '18 at 8:00
PᴇʜPᴇʜ
23.4k62951
23.4k62951
Thank you Peh, I have tried to code above. Also, thanks for explanation.
– Sevpoint
Nov 22 '18 at 8:16
add a comment |
Thank you Peh, I have tried to code above. Also, thanks for explanation.
– Sevpoint
Nov 22 '18 at 8:16
Thank you Peh, I have tried to code above. Also, thanks for explanation.
– Sevpoint
Nov 22 '18 at 8:16
Thank you Peh, I have tried to code above. Also, thanks for explanation.
– Sevpoint
Nov 22 '18 at 8:16
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53425924%2fsearch-string-in-a-range-text-template-and-replace-from-dynamic-rows%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I didn't test it, but I guess you need to get rid of
For a = 0 To UBound(Find_Text)loop and instead write three lines with replacing.– JohnyL
Nov 22 '18 at 7:46
Str is defined as a string. You assign to it once outside of the loops. In the loops you then repeatedly do replaces on str. As str is a vba variable which is not linked to your worksheet this may be where you have your error.
– Freeflow
Nov 22 '18 at 8:01