In my worksheet I have multiple values being replaced via VBA. Below is a piece of what I'm using. Out of curiosity can this be shortened?
If Cells(i, "B") = "600017334" Then Cells(i, "B") = "16273441"
If Cells(i, "B") = "600017340" Then Cells(i, "B") = "16273441"
If Cells(i, "B") = "600016206" Then Cells(i, "B") = "16273441"
If Cells(i, "B") = "600016252" Then Cells(i, "B") = "16273441"
If Cells(i, "B") = "600017302" Then Cells(i, "B") = "16273441"
You could use Select Case.
Note, more cases could be added to this if required.
Select Case Cells(i, "B").Value Case "600017334", "600017340", "600016206", "600016252", "600017302" Cells(i, "B") = "16273441" End Select
If posting code please use code tags, see here.
Thank! but if I were to continue this with another value is this what it would look like??
Select Case Cells(i, "B").Value
Case "600017334", "600017340", "600016206", "600016252", "600017302"
Cells(i, "B") = "16273441"
Case "600017334", "1234", "12345", "123456", "1234567"
Cells(i, "B") = "123456789"
End Select
Assuming that you don't have something like "600015000" in Column B range.
You can do something like...
"?" is used as wild card character to represent single character. Depending on your data, you can replace with single "*".If Cells(i, "B") Like "60001????" Then Cells(I, "B") = "16273441"
But without knowing range of values that can occur in Column B (and other criteria if any), it would be difficult to give you exact answer.
Last edited by CK76; 12-06-2016 at 02:24 PM. Reason: Grammer
Apologies I missed that value. This is what I meant to ask if I was to continue your example with another set of values I'd like to change
Select Case Cells(i, "B").Value
Case "600017334", "600017340", "600016206", "600016252", "600017302"
Cells(i, "B") = "16273441"
Case "123", "1234", "12345", "123456", "1234567"
Cells(i, "B") = "123456789"
End Select
yuenk,
Please take a few minutes to re-read the forum rules, and then edit all of your posts to add CODE tags.
Thanks.
Entia non sunt multiplicanda sine necessitate
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks