Find and Replace with multiple values for each occurrence (VBA)
0
I have SKUs that must be converted multiple times dependent on the number of pieces.
For example
original data:
- brp-100_cn_3pc_16x20
desired outcome:
brp-100a_cn_16x20
brp-100b_cn_16x20
brp-100c_cn_16x20
each in a separate cell in same column (notice 3pc = a,b,c for other SKUs 4pc = a,b,c,d…etc)
The data is being copied from a pivot table and pasted to another sheet. I recorded a Macro and added a For Each statement. It works for only the first instance not all the pasted SKUs.
Thanks in advance
Sub ReplaceEach()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlManual
Dim myrange As range
Set myrange = Sheets("PT_Data").range("K" & Rows.count).End(xlUp)
Dim i As Variant
Columns("K:K").Select
For Each i In myrange
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="a_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="b_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="c_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="d_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="e_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="f_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="g_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="h_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="i_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Next i
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlAutomatic
End Sub
excel vba replace find
edited Nov 15 '18 at 16:36
FunThomas
5,1361626
asked Nov 15 '18 at 16:27
A RossiA Rossi
276
add a comment |
0
I have SKUs that must be converted multiple times dependent on the number of pieces.
For example
original data:
- brp-100_cn_3pc_16x20
desired outcome:
brp-100a_cn_16x20
brp-100b_cn_16x20
brp-100c_cn_16x20
each in a separate cell in same column (notice 3pc = a,b,c for other SKUs 4pc = a,b,c,d…etc)
The data is being copied from a pivot table and pasted to another sheet. I recorded a Macro and added a For Each statement. It works for only the first instance not all the pasted SKUs.
Thanks in advance
Sub ReplaceEach()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlManual
Dim myrange As range
Set myrange = Sheets("PT_Data").range("K" & Rows.count).End(xlUp)
Dim i As Variant
Columns("K:K").Select
For Each i In myrange
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="a_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="b_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="c_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="d_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="e_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="f_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="g_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="h_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="i_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Next i
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlAutomatic
End Sub
excel vba replace find
edited Nov 15 '18 at 16:36
FunThomas
5,1361626
asked Nov 15 '18 at 16:27
A RossiA Rossi
276
1
You want to avoid usingActivate, the active cell is not always the one you think it is. Have a look at this
– cybernetic.nomad
Nov 15 '18 at 16:51
add a comment |
0
0
0
I have SKUs that must be converted multiple times dependent on the number of pieces.
For example
original data:
- brp-100_cn_3pc_16x20
desired outcome:
brp-100a_cn_16x20
brp-100b_cn_16x20
brp-100c_cn_16x20
each in a separate cell in same column (notice 3pc = a,b,c for other SKUs 4pc = a,b,c,d…etc)
The data is being copied from a pivot table and pasted to another sheet. I recorded a Macro and added a For Each statement. It works for only the first instance not all the pasted SKUs.
Thanks in advance
Sub ReplaceEach()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlManual
Dim myrange As range
Set myrange = Sheets("PT_Data").range("K" & Rows.count).End(xlUp)
Dim i As Variant
Columns("K:K").Select
For Each i In myrange
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="a_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="b_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="c_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="d_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="e_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="f_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="g_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="h_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="i_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Next i
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlAutomatic
End Sub
excel vba replace find
edited Nov 15 '18 at 16:36
FunThomas
5,1361626
asked Nov 15 '18 at 16:27
A RossiA Rossi
276
I have SKUs that must be converted multiple times dependent on the number of pieces.
For example
original data:
- brp-100_cn_3pc_16x20
desired outcome:
brp-100a_cn_16x20
brp-100b_cn_16x20
brp-100c_cn_16x20
each in a separate cell in same column (notice 3pc = a,b,c for other SKUs 4pc = a,b,c,d…etc)
The data is being copied from a pivot table and pasted to another sheet. I recorded a Macro and added a For Each statement. It works for only the first instance not all the pasted SKUs.
Thanks in advance
Sub ReplaceEach()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlManual
Dim myrange As range
Set myrange = Sheets("PT_Data").range("K" & Rows.count).End(xlUp)
Dim i As Variant
Columns("K:K").Select
For Each i In myrange
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="a_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="b_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="c_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="d_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="e_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="f_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="g_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="h_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Selection.Find(What:="_cn_9pc_12x12", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Replace What:="_cn_9pc_12x12", Replacement:="i_cn_12x12", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat _
:=False, ReplaceFormat:=False
Next i
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlAutomatic
End Sub
excel vba replace find
excel vba replace find
edited Nov 15 '18 at 16:36
FunThomas
5,1361626
asked Nov 15 '18 at 16:27
A RossiA Rossi
276
edited Nov 15 '18 at 16:36
FunThomas
5,1361626
asked Nov 15 '18 at 16:27
A RossiA Rossi
276
edited Nov 15 '18 at 16:36
FunThomas
5,1361626
edited Nov 15 '18 at 16:36
FunThomas
5,1361626
edited Nov 15 '18 at 16:36
FunThomas
5,1361626
5,1361626
asked Nov 15 '18 at 16:27
A RossiA Rossi
276
asked Nov 15 '18 at 16:27
A RossiA Rossi
276
asked Nov 15 '18 at 16:27
A RossiA Rossi
276
276
1
You want to avoid usingActivate, the active cell is not always the one you think it is. Have a look at this
– cybernetic.nomad
Nov 15 '18 at 16:51
add a comment |
1
You want to avoid usingActivate, the active cell is not always the one you think it is. Have a look at this
– cybernetic.nomad
Nov 15 '18 at 16:51
1
1
You want to avoid using
Activate, the active cell is not always the one you think it is. Have a look at this– cybernetic.nomad
Nov 15 '18 at 16:51
You want to avoid using
Activate, the active cell is not always the one you think it is. Have a look at this– cybernetic.nomad
Nov 15 '18 at 16:51
add a comment |
1 Answer
1
active
oldest
votes
2
You can use something like this:
Sub ExpandAll()
Dim c As Range, arr
'loop over the input values
For Each c In ActiveSheet.Range("B3:B8").Cells
arr = ExpandSKU(c.Value) '<< expand this SKU
'adjust destination to suit...
ActiveSheet.Cells(Rows.Count, 4).End(xlUp). _
Offset(1, 0).Resize(UBound(arr, 1), 1).Value = arr
c.Value = "" 'clear the original
Next c
End Sub
Function ExpandSKU(sku)
Dim arrSku, arrOut(), num, i As Long
arrSku = Split(sku, "_")
num = Replace(arrSku(2), "pc", "")
ReDim arrOut(1 To num, 1 To 1)
For i = 1 To num
arrOut(i, 1) = Join(Array(arrSku(0) & Chr(96 + i), _
arrSku(1), arrSku(3)), "_")
Next i
ExpandSKU = arrOut
End Function
answered Nov 15 '18 at 16:58
Tim WilliamsTim Williams
88.4k97087
Works great! But i also need the original SKU cleared/deleted from the column. Can't figure it out. I have never worked with Functions before. Thanks
– A Rossi
Nov 16 '18 at 14:33
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
});
}
});
draft saved
draft discarded
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%2f53323851%2ffind-and-replace-with-multiple-values-for-each-occurrence-vba%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
2
You can use something like this:
Sub ExpandAll()
Dim c As Range, arr
'loop over the input values
For Each c In ActiveSheet.Range("B3:B8").Cells
arr = ExpandSKU(c.Value) '<< expand this SKU
'adjust destination to suit...
ActiveSheet.Cells(Rows.Count, 4).End(xlUp). _
Offset(1, 0).Resize(UBound(arr, 1), 1).Value = arr
c.Value = "" 'clear the original
Next c
End Sub
Function ExpandSKU(sku)
Dim arrSku, arrOut(), num, i As Long
arrSku = Split(sku, "_")
num = Replace(arrSku(2), "pc", "")
ReDim arrOut(1 To num, 1 To 1)
For i = 1 To num
arrOut(i, 1) = Join(Array(arrSku(0) & Chr(96 + i), _
arrSku(1), arrSku(3)), "_")
Next i
ExpandSKU = arrOut
End Function
answered Nov 15 '18 at 16:58
Tim WilliamsTim Williams
88.4k97087
Works great! But i also need the original SKU cleared/deleted from the column. Can't figure it out. I have never worked with Functions before. Thanks
– A Rossi
Nov 16 '18 at 14:33
add a comment |
2
You can use something like this:
Sub ExpandAll()
Dim c As Range, arr
'loop over the input values
For Each c In ActiveSheet.Range("B3:B8").Cells
arr = ExpandSKU(c.Value) '<< expand this SKU
'adjust destination to suit...
ActiveSheet.Cells(Rows.Count, 4).End(xlUp). _
Offset(1, 0).Resize(UBound(arr, 1), 1).Value = arr
c.Value = "" 'clear the original
Next c
End Sub
Function ExpandSKU(sku)
Dim arrSku, arrOut(), num, i As Long
arrSku = Split(sku, "_")
num = Replace(arrSku(2), "pc", "")
ReDim arrOut(1 To num, 1 To 1)
For i = 1 To num
arrOut(i, 1) = Join(Array(arrSku(0) & Chr(96 + i), _
arrSku(1), arrSku(3)), "_")
Next i
ExpandSKU = arrOut
End Function
answered Nov 15 '18 at 16:58
Tim WilliamsTim Williams
88.4k97087
Works great! But i also need the original SKU cleared/deleted from the column. Can't figure it out. I have never worked with Functions before. Thanks
– A Rossi
Nov 16 '18 at 14:33
add a comment |
2
2
2
You can use something like this:
Sub ExpandAll()
Dim c As Range, arr
'loop over the input values
For Each c In ActiveSheet.Range("B3:B8").Cells
arr = ExpandSKU(c.Value) '<< expand this SKU
'adjust destination to suit...
ActiveSheet.Cells(Rows.Count, 4).End(xlUp). _
Offset(1, 0).Resize(UBound(arr, 1), 1).Value = arr
c.Value = "" 'clear the original
Next c
End Sub
Function ExpandSKU(sku)
Dim arrSku, arrOut(), num, i As Long
arrSku = Split(sku, "_")
num = Replace(arrSku(2), "pc", "")
ReDim arrOut(1 To num, 1 To 1)
For i = 1 To num
arrOut(i, 1) = Join(Array(arrSku(0) & Chr(96 + i), _
arrSku(1), arrSku(3)), "_")
Next i
ExpandSKU = arrOut
End Function
answered Nov 15 '18 at 16:58
Tim WilliamsTim Williams
88.4k97087
You can use something like this:
Sub ExpandAll()
Dim c As Range, arr
'loop over the input values
For Each c In ActiveSheet.Range("B3:B8").Cells
arr = ExpandSKU(c.Value) '<< expand this SKU
'adjust destination to suit...
ActiveSheet.Cells(Rows.Count, 4).End(xlUp). _
Offset(1, 0).Resize(UBound(arr, 1), 1).Value = arr
c.Value = "" 'clear the original
Next c
End Sub
Function ExpandSKU(sku)
Dim arrSku, arrOut(), num, i As Long
arrSku = Split(sku, "_")
num = Replace(arrSku(2), "pc", "")
ReDim arrOut(1 To num, 1 To 1)
For i = 1 To num
arrOut(i, 1) = Join(Array(arrSku(0) & Chr(96 + i), _
arrSku(1), arrSku(3)), "_")
Next i
ExpandSKU = arrOut
End Function
answered Nov 15 '18 at 16:58
Tim WilliamsTim Williams
88.4k97087
edited Nov 16 '18 at 15:38
answered Nov 15 '18 at 16:58
Tim WilliamsTim Williams
88.4k97087
answered Nov 15 '18 at 16:58
Tim WilliamsTim Williams
88.4k97087
answered Nov 15 '18 at 16:58
Tim WilliamsTim Williams
88.4k97087
88.4k97087
Works great! But i also need the original SKU cleared/deleted from the column. Can't figure it out. I have never worked with Functions before. Thanks
– A Rossi
Nov 16 '18 at 14:33
add a comment |
Works great! But i also need the original SKU cleared/deleted from the column. Can't figure it out. I have never worked with Functions before. Thanks
– A Rossi
Nov 16 '18 at 14:33
Works great! But i also need the original SKU cleared/deleted from the column. Can't figure it out. I have never worked with Functions before. Thanks
– A Rossi
Nov 16 '18 at 14:33
Works great! But i also need the original SKU cleared/deleted from the column. Can't figure it out. I have never worked with Functions before. Thanks
– A Rossi
Nov 16 '18 at 14:33
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53323851%2ffind-and-replace-with-multiple-values-for-each-occurrence-vba%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
1
You want to avoid using
Activate, the active cell is not always the one you think it is. Have a look at this– cybernetic.nomad
Nov 15 '18 at 16:51