VBA: How to replace entire Excel cell value basing on its content?

I have an Excel Spreadsheet with some names in a cells. There can be as one name per cell as multiple names in one cell.

For example: Cell A5 contains "Joe", cell BD54 contains "Joe;Harry;Molly", cell YY1 contains "Harry;Butch".

What I wan to achieve is to replace values of all cells that contains "Joe" or "Molly" with "1, regardless of what else is in this cells, and celss that contains just "Harry" or "Butch" will be replaced with "0" if it does not contain "Joe" or "Molly". For example above, result should be as follows: A5 conains "1", BD54 contains "1", YY1 contains "0".

I'm a total newbie in VBA - would you, gents, please assis with that? I suspect that Cells.Replace should be used here, however, I'm not sure how to pass a list of names to it

Dim Findtext As String

 Dim Replacetext As String

 Findtext = "Joe","Molly"

 Replacetext = "1"

 Findtext = "Harry","Butch"

 Replacetext = "0"

 Cells.Replace What:=Findtext, Replacement:=Replacetext, LookAt:=xlPart, _

 SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

 ReplaceFormat:=False

edited Sep 26 '14 at 10:13

asked Sep 26 '14 at 9:05

Volkodav2003

1613

bumped to the homepage by Community♦ 2 days ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

Welcome to SU! What have you already achieved, it would be nice to take a look at your result?
– duDE
Sep 26 '14 at 9:15

1

Not sure why this was down-voted. This question shows effort to solve and an indication as to where OP is stuck. Maybe the language isn't crystal clear, but it's still a good effort.
– Raystafarian
Sep 26 '14 at 12:17

add a comment |

I have an Excel Spreadsheet with some names in a cells. There can be as one name per cell as multiple names in one cell.

For example: Cell A5 contains "Joe", cell BD54 contains "Joe;Harry;Molly", cell YY1 contains "Harry;Butch".

I'm a total newbie in VBA - would you, gents, please assis with that? I suspect that Cells.Replace should be used here, however, I'm not sure how to pass a list of names to it

Dim Findtext As String

 Dim Replacetext As String

 Findtext = "Joe","Molly"

 Replacetext = "1"

 Findtext = "Harry","Butch"

 Replacetext = "0"

 Cells.Replace What:=Findtext, Replacement:=Replacetext, LookAt:=xlPart, _

 SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

 ReplaceFormat:=False

edited Sep 26 '14 at 10:13

asked Sep 26 '14 at 9:05

Volkodav2003

1613

bumped to the homepage by Community♦ 2 days ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

Welcome to SU! What have you already achieved, it would be nice to take a look at your result?
– duDE
Sep 26 '14 at 9:15

1

Not sure why this was down-voted. This question shows effort to solve and an indication as to where OP is stuck. Maybe the language isn't crystal clear, but it's still a good effort.
– Raystafarian
Sep 26 '14 at 12:17

add a comment |

I have an Excel Spreadsheet with some names in a cells. There can be as one name per cell as multiple names in one cell.

For example: Cell A5 contains "Joe", cell BD54 contains "Joe;Harry;Molly", cell YY1 contains "Harry;Butch".

I'm a total newbie in VBA - would you, gents, please assis with that? I suspect that Cells.Replace should be used here, however, I'm not sure how to pass a list of names to it

Dim Findtext As String

 Dim Replacetext As String

 Findtext = "Joe","Molly"

 Replacetext = "1"

 Findtext = "Harry","Butch"

 Replacetext = "0"

 Cells.Replace What:=Findtext, Replacement:=Replacetext, LookAt:=xlPart, _

 SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

 ReplaceFormat:=False

edited Sep 26 '14 at 10:13

asked Sep 26 '14 at 9:05

Volkodav2003

1613

I have an Excel Spreadsheet with some names in a cells. There can be as one name per cell as multiple names in one cell.

For example: Cell A5 contains "Joe", cell BD54 contains "Joe;Harry;Molly", cell YY1 contains "Harry;Butch".

I'm a total newbie in VBA - would you, gents, please assis with that? I suspect that Cells.Replace should be used here, however, I'm not sure how to pass a list of names to it

Dim Findtext As String

 Dim Replacetext As String

 Findtext = "Joe","Molly"

 Replacetext = "1"

 Findtext = "Harry","Butch"

 Replacetext = "0"

 Cells.Replace What:=Findtext, Replacement:=Replacetext, LookAt:=xlPart, _

 SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

 ReplaceFormat:=False

microsoft-excel microsoft-excel-2010 worksheet-function microsoft-excel-2007 vba

edited Sep 26 '14 at 10:13

asked Sep 26 '14 at 9:05

Volkodav2003

1613

edited Sep 26 '14 at 10:13

asked Sep 26 '14 at 9:05

Volkodav2003

1613

edited Sep 26 '14 at 10:13

asked Sep 26 '14 at 9:05

Volkodav2003

1613

asked Sep 26 '14 at 9:05

Volkodav2003

1613

asked Sep 26 '14 at 9:05

Volkodav2003

1613

bumped to the homepage by Community♦ 2 days ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

bumped to the homepage by Community♦ 2 days ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

Welcome to SU! What have you already achieved, it would be nice to take a look at your result?
– duDE
Sep 26 '14 at 9:15

1

Not sure why this was down-voted. This question shows effort to solve and an indication as to where OP is stuck. Maybe the language isn't crystal clear, but it's still a good effort.
– Raystafarian
Sep 26 '14 at 12:17

add a comment |

Welcome to SU! What have you already achieved, it would be nice to take a look at your result?
– duDE
Sep 26 '14 at 9:15

1

Not sure why this was down-voted. This question shows effort to solve and an indication as to where OP is stuck. Maybe the language isn't crystal clear, but it's still a good effort.
– Raystafarian
Sep 26 '14 at 12:17

Welcome to SU! What have you already achieved, it would be nice to take a look at your result?
– duDE
Sep 26 '14 at 9:15

Not sure why this was down-voted. This question shows effort to solve and an indication as to where OP is stuck. Maybe the language isn't crystal clear, but it's still a good effort.
– Raystafarian
Sep 26 '14 at 12:17

add a comment |

1 Answer
1

active

oldest

votes

You're on the right track, but instead you can do something like this, using instr -

Sub test()

Dim check1 As Integer

Dim check2 As Integer

Dim find1 As String

find1 = "a"

Dim find2 As String

find2 = "b"

For Each c In Range("A:A")

    check1 = InStr(1, c, find1, 1)

    check2 = InStr(1, c, find2, 1)

        If check1 > 0 Then

            c.Value = 1

        End If

        If check2 > 0 Then

            c.Value = 2

        End If

Next



End Sub

Basically, it looks in the cell for the position of the search string. If it doesn't find it, it reverts to 0. So if it does find it, the check1 or check2 integers will be >0. If it find both strings, it will revert to the second (or last) if integer.

answered Sep 26 '14 at 12:13

Raystafarian

19.4k104989

So if I have 15 names of "Joe" type and 15 names of "Harry" type, I will need to use smth like ` Dim find1 As String find1 = "a" Dim find2 As String find2 = "b" ... Dim find30 As String find30 = "Z" `
– Volkodav2003
Sep 26 '14 at 14:53

dim find1 as string _ find1 = "Joe" @Volkodav2003 - or just use the string in the instr() in place of the string variable
– Raystafarian
Sep 26 '14 at 14:54

I need to be careful with "Return" key on this site... So if I have 30 names I should do 30 comparisons? Or I can simplify this somehow keeping in mind that this 30 names can be divided into 2 groups? Some pseudocode as I not found yet how to implement this: "Joe", "Mary", "Jim" BelongsTo "MyTeam" group; "Harry", "Butch","Jake" BelongsTo "OtherTeam" group; Check CellValue; If CellValue Contains MyTeamGroupMember string, then replace CellValue with 1; Next; If CellValue Contains OtherTeamGroupMember string, then replace CellValue with 0
– Volkodav2003
Sep 26 '14 at 15:08

@Volkodav2003 no, instr only works with one string, not multiple. one instr per comparison
– Raystafarian
Sep 26 '14 at 15:11

So pity. Well, I'll try and let you know, how it works
– Volkodav2003
Sep 26 '14 at 15:37

|
show 2 more comments

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f817072%2fvba-how-to-replace-entire-excel-cell-value-basing-on-its-content%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

You're on the right track, but instead you can do something like this, using instr -

Sub test()

Dim check1 As Integer

Dim check2 As Integer

Dim find1 As String

find1 = "a"

Dim find2 As String

find2 = "b"

For Each c In Range("A:A")

    check1 = InStr(1, c, find1, 1)

    check2 = InStr(1, c, find2, 1)

        If check1 > 0 Then

            c.Value = 1

        End If

        If check2 > 0 Then

            c.Value = 2

        End If

Next



End Sub

answered Sep 26 '14 at 12:13

Raystafarian

19.4k104989

So if I have 15 names of "Joe" type and 15 names of "Harry" type, I will need to use smth like ` Dim find1 As String find1 = "a" Dim find2 As String find2 = "b" ... Dim find30 As String find30 = "Z" `
– Volkodav2003
Sep 26 '14 at 14:53

dim find1 as string _ find1 = "Joe" @Volkodav2003 - or just use the string in the instr() in place of the string variable
– Raystafarian
Sep 26 '14 at 14:54

I need to be careful with "Return" key on this site... So if I have 30 names I should do 30 comparisons? Or I can simplify this somehow keeping in mind that this 30 names can be divided into 2 groups? Some pseudocode as I not found yet how to implement this: "Joe", "Mary", "Jim" BelongsTo "MyTeam" group; "Harry", "Butch","Jake" BelongsTo "OtherTeam" group; Check CellValue; If CellValue Contains MyTeamGroupMember string, then replace CellValue with 1; Next; If CellValue Contains OtherTeamGroupMember string, then replace CellValue with 0
– Volkodav2003
Sep 26 '14 at 15:08

@Volkodav2003 no, instr only works with one string, not multiple. one instr per comparison
– Raystafarian
Sep 26 '14 at 15:11

So pity. Well, I'll try and let you know, how it works
– Volkodav2003
Sep 26 '14 at 15:37

|
show 2 more comments

You're on the right track, but instead you can do something like this, using instr -

Sub test()

Dim check1 As Integer

Dim check2 As Integer

Dim find1 As String

find1 = "a"

Dim find2 As String

find2 = "b"

For Each c In Range("A:A")

    check1 = InStr(1, c, find1, 1)

    check2 = InStr(1, c, find2, 1)

        If check1 > 0 Then

            c.Value = 1

        End If

        If check2 > 0 Then

            c.Value = 2

        End If

Next



End Sub

answered Sep 26 '14 at 12:13

Raystafarian

19.4k104989

So if I have 15 names of "Joe" type and 15 names of "Harry" type, I will need to use smth like ` Dim find1 As String find1 = "a" Dim find2 As String find2 = "b" ... Dim find30 As String find30 = "Z" `
– Volkodav2003
Sep 26 '14 at 14:53

dim find1 as string _ find1 = "Joe" @Volkodav2003 - or just use the string in the instr() in place of the string variable
– Raystafarian
Sep 26 '14 at 14:54

I need to be careful with "Return" key on this site... So if I have 30 names I should do 30 comparisons? Or I can simplify this somehow keeping in mind that this 30 names can be divided into 2 groups? Some pseudocode as I not found yet how to implement this: "Joe", "Mary", "Jim" BelongsTo "MyTeam" group; "Harry", "Butch","Jake" BelongsTo "OtherTeam" group; Check CellValue; If CellValue Contains MyTeamGroupMember string, then replace CellValue with 1; Next; If CellValue Contains OtherTeamGroupMember string, then replace CellValue with 0
– Volkodav2003
Sep 26 '14 at 15:08

@Volkodav2003 no, instr only works with one string, not multiple. one instr per comparison
– Raystafarian
Sep 26 '14 at 15:11

So pity. Well, I'll try and let you know, how it works
– Volkodav2003
Sep 26 '14 at 15:37

|
show 2 more comments

You're on the right track, but instead you can do something like this, using instr -

Sub test()

Dim check1 As Integer

Dim check2 As Integer

Dim find1 As String

find1 = "a"

Dim find2 As String

find2 = "b"

For Each c In Range("A:A")

    check1 = InStr(1, c, find1, 1)

    check2 = InStr(1, c, find2, 1)

        If check1 > 0 Then

            c.Value = 1

        End If

        If check2 > 0 Then

            c.Value = 2

        End If

Next



End Sub

answered Sep 26 '14 at 12:13

Raystafarian

19.4k104989

You're on the right track, but instead you can do something like this, using instr -

Sub test()

Dim check1 As Integer

Dim check2 As Integer

Dim find1 As String

find1 = "a"

Dim find2 As String

find2 = "b"

For Each c In Range("A:A")

    check1 = InStr(1, c, find1, 1)

    check2 = InStr(1, c, find2, 1)

        If check1 > 0 Then

            c.Value = 1

        End If

        If check2 > 0 Then

            c.Value = 2

        End If

Next



End Sub

answered Sep 26 '14 at 12:13

Raystafarian

19.4k104989

answered Sep 26 '14 at 12:13

Raystafarian

19.4k104989

answered Sep 26 '14 at 12:13

Raystafarian

19.4k104989

answered Sep 26 '14 at 12:13

Raystafarian

19.4k104989

So if I have 15 names of "Joe" type and 15 names of "Harry" type, I will need to use smth like ` Dim find1 As String find1 = "a" Dim find2 As String find2 = "b" ... Dim find30 As String find30 = "Z" `
– Volkodav2003
Sep 26 '14 at 14:53

dim find1 as string _ find1 = "Joe" @Volkodav2003 - or just use the string in the instr() in place of the string variable
– Raystafarian
Sep 26 '14 at 14:54

I need to be careful with "Return" key on this site... So if I have 30 names I should do 30 comparisons? Or I can simplify this somehow keeping in mind that this 30 names can be divided into 2 groups? Some pseudocode as I not found yet how to implement this: "Joe", "Mary", "Jim" BelongsTo "MyTeam" group; "Harry", "Butch","Jake" BelongsTo "OtherTeam" group; Check CellValue; If CellValue Contains MyTeamGroupMember string, then replace CellValue with 1; Next; If CellValue Contains OtherTeamGroupMember string, then replace CellValue with 0
– Volkodav2003
Sep 26 '14 at 15:08

@Volkodav2003 no, instr only works with one string, not multiple. one instr per comparison
– Raystafarian
Sep 26 '14 at 15:11

So pity. Well, I'll try and let you know, how it works
– Volkodav2003
Sep 26 '14 at 15:37

|
show 2 more comments

So if I have 15 names of "Joe" type and 15 names of "Harry" type, I will need to use smth like ` Dim find1 As String find1 = "a" Dim find2 As String find2 = "b" ... Dim find30 As String find30 = "Z" `
– Volkodav2003
Sep 26 '14 at 14:53

dim find1 as string _ find1 = "Joe" @Volkodav2003 - or just use the string in the instr() in place of the string variable
– Raystafarian
Sep 26 '14 at 14:54

I need to be careful with "Return" key on this site... So if I have 30 names I should do 30 comparisons? Or I can simplify this somehow keeping in mind that this 30 names can be divided into 2 groups? Some pseudocode as I not found yet how to implement this: "Joe", "Mary", "Jim" BelongsTo "MyTeam" group; "Harry", "Butch","Jake" BelongsTo "OtherTeam" group; Check CellValue; If CellValue Contains MyTeamGroupMember string, then replace CellValue with 1; Next; If CellValue Contains OtherTeamGroupMember string, then replace CellValue with 0
– Volkodav2003
Sep 26 '14 at 15:08

@Volkodav2003 no, instr only works with one string, not multiple. one instr per comparison
– Raystafarian
Sep 26 '14 at 15:11

So pity. Well, I'll try and let you know, how it works
– Volkodav2003
Sep 26 '14 at 15:37

So if I have 15 names of "Joe" type and 15 names of "Harry" type, I will need to use smth like ` Dim find1 As String find1 = "a" Dim find2 As String find2 = "b" ... Dim find30 As String find30 = "Z" `
– Volkodav2003
Sep 26 '14 at 14:53

dim find1 as string _ find1 = "Joe" @Volkodav2003 - or just use the string in the instr() in place of the string variable
– Raystafarian
Sep 26 '14 at 14:54

I need to be careful with "Return" key on this site... So if I have 30 names I should do 30 comparisons? Or I can simplify this somehow keeping in mind that this 30 names can be divided into 2 groups? Some pseudocode as I not found yet how to implement this:

"Joe", "Mary", "Jim" BelongsTo "MyTeam" group; "Harry", "Butch","Jake" BelongsTo "OtherTeam" group; Check CellValue; If CellValue Contains MyTeamGroupMember string, then replace CellValue with 1; Next; If CellValue Contains OtherTeamGroupMember string, then replace CellValue with 0

– Volkodav2003
Sep 26 '14 at 15:08

"Joe", "Mary", "Jim" BelongsTo "MyTeam" group; "Harry", "Butch","Jake" BelongsTo "OtherTeam" group; Check CellValue; If CellValue Contains MyTeamGroupMember string, then replace CellValue with 1; Next; If CellValue Contains OtherTeamGroupMember string, then replace CellValue with 0

– Volkodav2003
Sep 26 '14 at 15:08

@Volkodav2003 no, instr only works with one string, not multiple. one instr per comparison
– Raystafarian
Sep 26 '14 at 15:11

So pity. Well, I'll try and let you know, how it works
– Volkodav2003
Sep 26 '14 at 15:37

|
show 2 more comments

draft saved

draft discarded

Thanks for contributing an answer to Super User!

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.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

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');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Ytdyklly