VBA Find&Replace cell content based on a lookup table using offset










0















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?










share|improve this question



















  • 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
















0















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?










share|improve this question



















  • 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














0












0








0








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?










share|improve this question
















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 13 '18 at 21:16









vencaslac

1,004217




1,004217










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













  • 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













1 Answer
1






active

oldest

votes


















0














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






share|improve this answer























  • 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










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


















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









0














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






share|improve this answer























  • 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















0














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






share|improve this answer























  • 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













0












0








0







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






share|improve this answer













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







share|improve this answer












share|improve this answer



share|improve this answer










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

















  • 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

















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














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





















































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







Popular posts from this blog

Top Tejano songwriter Luis Silva dead of heart attack at 64

政党

天津地下鉄3号線