VBA Find&Replace cell content based on a lookup table using offset
Scratching my head over this simple issue: I have a sub to find and replace a value in my Sheet.Sheet1 column F based on a table in Sheet.Classification column B. The value is replaced by the content of Classification Column F, hence the Offset(0, 4).
The sub works fine excepts that I want it to replace the entire content of my cell in column F in Sheet 1 (rather than just the Lookup.Value found).
Sub ABC()
Dim rngData As Range
Dim rngLookup As Range
Dim Lookup As Range
With Sheets("Sheet1")
Set rngData = .Range("F2", .Range("F" & Rows.Count).End(xlUp))
End With
With Sheets("Classification")
Set rngLookup = .Range("B2", .Range("B" & Rows.Count).End(xlUp))
End With
For Each Lookup In rngLookup
If Lookup.Value = "" Then
rngData.Replace what:=Lookup.Value, _
Replacement:=Lookup.Offset(0, 4).Value, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End If
Next Lookup
End Sub
Any help?
excel vba
edited Nov 13 '18 at 21:16
vencaslac
1,002217
asked Nov 13 '18 at 17:32
mkpersonalmkpersonal
1
add a comment |
Scratching my head over this simple issue: I have a sub to find and replace a value in my Sheet.Sheet1 column F based on a table in Sheet.Classification column B. The value is replaced by the content of Classification Column F, hence the Offset(0, 4).
The sub works fine excepts that I want it to replace the entire content of my cell in column F in Sheet 1 (rather than just the Lookup.Value found).
Sub ABC()
Dim rngData As Range
Dim rngLookup As Range
Dim Lookup As Range
With Sheets("Sheet1")
Set rngData = .Range("F2", .Range("F" & Rows.Count).End(xlUp))
End With
With Sheets("Classification")
Set rngLookup = .Range("B2", .Range("B" & Rows.Count).End(xlUp))
End With
For Each Lookup In rngLookup
If Lookup.Value = "" Then
rngData.Replace what:=Lookup.Value, _
Replacement:=Lookup.Offset(0, 4).Value, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End If
Next Lookup
End Sub
Any help?
excel vba
edited Nov 13 '18 at 21:16
vencaslac
1,002217
asked Nov 13 '18 at 17:32
mkpersonalmkpersonal
1
1
So rngData.replace means you want to replace the data in the same row as lookup? It's a bit unclear. Maybe you can add an example?
– Andreas
Nov 13 '18 at 17:40
Yes that's right. Let's say in Sheet1, F2 I have "First Prize is for the winner" and in the sheetClassification, B2 I have "First Prize" I want my macro to grab the content of C2 (still from Classification) and insert it in B2 (of Sheet1). Hence, if there is a match between my two sheets, I the cell content of my original sheet to be replaced by my offset value. Is this more clear now?
– mkpersonal
Nov 14 '18 at 12:35
add a comment |
Scratching my head over this simple issue: I have a sub to find and replace a value in my Sheet.Sheet1 column F based on a table in Sheet.Classification column B. The value is replaced by the content of Classification Column F, hence the Offset(0, 4).
The sub works fine excepts that I want it to replace the entire content of my cell in column F in Sheet 1 (rather than just the Lookup.Value found).
Sub ABC()
Dim rngData As Range
Dim rngLookup As Range
Dim Lookup As Range
With Sheets("Sheet1")
Set rngData = .Range("F2", .Range("F" & Rows.Count).End(xlUp))
End With
With Sheets("Classification")
Set rngLookup = .Range("B2", .Range("B" & Rows.Count).End(xlUp))
End With
For Each Lookup In rngLookup
If Lookup.Value = "" Then
rngData.Replace what:=Lookup.Value, _
Replacement:=Lookup.Offset(0, 4).Value, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End If
Next Lookup
End Sub
Any help?
excel vba
edited Nov 13 '18 at 21:16
vencaslac
1,002217
asked Nov 13 '18 at 17:32
mkpersonalmkpersonal
1
Scratching my head over this simple issue: I have a sub to find and replace a value in my Sheet.Sheet1 column F based on a table in Sheet.Classification column B. The value is replaced by the content of Classification Column F, hence the Offset(0, 4).
The sub works fine excepts that I want it to replace the entire content of my cell in column F in Sheet 1 (rather than just the Lookup.Value found).
Sub ABC()
Dim rngData As Range
Dim rngLookup As Range
Dim Lookup As Range
With Sheets("Sheet1")
Set rngData = .Range("F2", .Range("F" & Rows.Count).End(xlUp))
End With
With Sheets("Classification")
Set rngLookup = .Range("B2", .Range("B" & Rows.Count).End(xlUp))
End With
For Each Lookup In rngLookup
If Lookup.Value = "" Then
rngData.Replace what:=Lookup.Value, _
Replacement:=Lookup.Offset(0, 4).Value, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End If
Next Lookup
End Sub
Any help?
excel vba
excel vba
edited Nov 13 '18 at 21:16
vencaslac
1,002217
asked Nov 13 '18 at 17:32
mkpersonalmkpersonal
1
edited Nov 13 '18 at 21:16
vencaslac
1,002217
asked Nov 13 '18 at 17:32
mkpersonalmkpersonal
1
edited Nov 13 '18 at 21:16
vencaslac
1,002217
edited Nov 13 '18 at 21:16
vencaslac
1,002217
edited Nov 13 '18 at 21:16
vencaslac
1,002217
1,002217
asked Nov 13 '18 at 17:32
mkpersonalmkpersonal
1
asked Nov 13 '18 at 17:32
mkpersonalmkpersonal
1
asked Nov 13 '18 at 17:32
mkpersonalmkpersonal
1
1
1
So rngData.replace means you want to replace the data in the same row as lookup? It's a bit unclear. Maybe you can add an example?
– Andreas
Nov 13 '18 at 17:40
Yes that's right. Let's say in Sheet1, F2 I have "First Prize is for the winner" and in the sheetClassification, B2 I have "First Prize" I want my macro to grab the content of C2 (still from Classification) and insert it in B2 (of Sheet1). Hence, if there is a match between my two sheets, I the cell content of my original sheet to be replaced by my offset value. Is this more clear now?
– mkpersonal
Nov 14 '18 at 12:35
add a comment |
1
So rngData.replace means you want to replace the data in the same row as lookup? It's a bit unclear. Maybe you can add an example?
– Andreas
Nov 13 '18 at 17:40
Yes that's right. Let's say in Sheet1, F2 I have "First Prize is for the winner" and in the sheetClassification, B2 I have "First Prize" I want my macro to grab the content of C2 (still from Classification) and insert it in B2 (of Sheet1). Hence, if there is a match between my two sheets, I the cell content of my original sheet to be replaced by my offset value. Is this more clear now?
– mkpersonal
Nov 14 '18 at 12:35
1
1
So rngData.replace means you want to replace the data in the same row as lookup? It's a bit unclear. Maybe you can add an example?
– Andreas
Nov 13 '18 at 17:40
So rngData.replace means you want to replace the data in the same row as lookup? It's a bit unclear. Maybe you can add an example?
– Andreas
Nov 13 '18 at 17:40
Yes that's right. Let's say in Sheet1, F2 I have "First Prize is for the winner" and in the sheetClassification, B2 I have "First Prize" I want my macro to grab the content of C2 (still from Classification) and insert it in B2 (of Sheet1). Hence, if there is a match between my two sheets, I the cell content of my original sheet to be replaced by my offset value. Is this more clear now?
– mkpersonal
Nov 14 '18 at 12:35
Yes that's right. Let's say in Sheet1, F2 I have "First Prize is for the winner" and in the sheetClassification, B2 I have "First Prize" I want my macro to grab the content of C2 (still from Classification) and insert it in B2 (of Sheet1). Hence, if there is a match between my two sheets, I the cell content of my original sheet to be replaced by my offset value. Is this more clear now?
– mkpersonal
Nov 14 '18 at 12:35
add a comment |
1 Answer
1
active
oldest
votes
I think you need to modify the IF statement:
If Lookup.Value = "" Then
rngData.Value =Lookup.Offset(0, 4).Value
End If
Using the .Replace method is like the REPLACE() function, it will swap out parts of a string and not the whole contents. See: https://docs.microsoft.com/en-us/office/vba/api/excel.range.replace
answered Nov 13 '18 at 20:01
MattMatt
111
I have tried this and it now replaces all cell content but not with teh right value. Any idea why?
– mkpersonal
Nov 14 '18 at 16:22
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%2f53286591%2fvba-findreplace-cell-content-based-on-a-lookup-table-using-offset%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
I think you need to modify the IF statement:
If Lookup.Value = "" Then
rngData.Value =Lookup.Offset(0, 4).Value
End If
Using the .Replace method is like the REPLACE() function, it will swap out parts of a string and not the whole contents. See: https://docs.microsoft.com/en-us/office/vba/api/excel.range.replace
answered Nov 13 '18 at 20:01
MattMatt
111
I have tried this and it now replaces all cell content but not with teh right value. Any idea why?
– mkpersonal
Nov 14 '18 at 16:22
add a comment |
I think you need to modify the IF statement:
If Lookup.Value = "" Then
rngData.Value =Lookup.Offset(0, 4).Value
End If
Using the .Replace method is like the REPLACE() function, it will swap out parts of a string and not the whole contents. See: https://docs.microsoft.com/en-us/office/vba/api/excel.range.replace
answered Nov 13 '18 at 20:01
MattMatt
111
I have tried this and it now replaces all cell content but not with teh right value. Any idea why?
– mkpersonal
Nov 14 '18 at 16:22
add a comment |
I think you need to modify the IF statement:
If Lookup.Value = "" Then
rngData.Value =Lookup.Offset(0, 4).Value
End If
Using the .Replace method is like the REPLACE() function, it will swap out parts of a string and not the whole contents. See: https://docs.microsoft.com/en-us/office/vba/api/excel.range.replace
answered Nov 13 '18 at 20:01
MattMatt
111
I think you need to modify the IF statement:
If Lookup.Value = "" Then
rngData.Value =Lookup.Offset(0, 4).Value
End If
Using the .Replace method is like the REPLACE() function, it will swap out parts of a string and not the whole contents. See: https://docs.microsoft.com/en-us/office/vba/api/excel.range.replace
answered Nov 13 '18 at 20:01
MattMatt
111
answered Nov 13 '18 at 20:01
MattMatt
111
answered Nov 13 '18 at 20:01
MattMatt
111
answered Nov 13 '18 at 20:01
MattMatt
111
111
I have tried this and it now replaces all cell content but not with teh right value. Any idea why?
– mkpersonal
Nov 14 '18 at 16:22
add a comment |
I have tried this and it now replaces all cell content but not with teh right value. Any idea why?
– mkpersonal
Nov 14 '18 at 16:22
I have tried this and it now replaces all cell content but not with teh right value. Any idea why?
– mkpersonal
Nov 14 '18 at 16:22
I have tried this and it now replaces all cell content but not with teh right value. Any idea why?
– mkpersonal
Nov 14 '18 at 16:22
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%2f53286591%2fvba-findreplace-cell-content-based-on-a-lookup-table-using-offset%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
So rngData.replace means you want to replace the data in the same row as lookup? It's a bit unclear. Maybe you can add an example?
– Andreas
Nov 13 '18 at 17:40
Yes that's right. Let's say in Sheet1, F2 I have "First Prize is for the winner" and in the sheetClassification, B2 I have "First Prize" I want my macro to grab the content of C2 (still from Classification) and insert it in B2 (of Sheet1). Hence, if there is a match between my two sheets, I the cell content of my original sheet to be replaced by my offset value. Is this more clear now?
– mkpersonal
Nov 14 '18 at 12:35