Shortening VBA

[yuenk]

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"

[Norie]

You could use Select Case.

Please Login or Register  to view this content.

Note, more cases could be added to this if required.

[yuenk]

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

[CK76]

Assuming that you don't have something like "600015000" in Column B range.

You can do something like...

Please Login or Register  to view this content.

"?" is used as wild card character to represent single character. Depending on your data, you can replace with single "*".

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.

[yuenk]

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

[shg]

yuenk,

Please take a few minutes to re-read the forum rules, and then edit all of your posts to add CODE tags.

Thanks.