VBA - conditional value replacement

Question

I have a spreadsheet with values in multiple columns “A:Z” I want to find and replace values in Column "B" depending on the value in column "B" and column "C". For example:

If Column B = SWI and Column C = IN then replace SWI with Switch Subscription

But if Column B = SWI and Column C = OUT then replace SWI with Switch Redemption

The table looks like that:

Col B       Col C
RED         OUT
RED         OUT
SWI         IN
SWI         OUT
SWI         IN
SWI         OUT
SUB         IN

I tried to write the code but it didn't work! Where is the problem?

N = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To N
        v1 = Cells(i, 2).Value
        v3 = Cells(i, 3).Value
        If v1 = "SWI" And v3 = "IN" Then Cells(i, 2).Value = "Switch Subscription"
        If v1 = "SWI" And v3 = "OUT" Then Cells(i, 2).Value = "Switch Redemption"
    Next i

Is the data in Column A the same length as Column B and C, As in are there the same number of rows of data in A as there is in B and C? — Scott Craner, Apr 08 '16 at 14:04
You did not explicitly express which sheet should be used, your code can fail if it is applied on the wrong sheet. — Vincent G, Apr 08 '16 at 14:07
See[HERE](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — Scott Craner, Apr 08 '16 at 14:11
I tried to add "For Each sheet In ActiveWorkbook.Worksheets" but it didn't work neither — shinpencil, Apr 08 '16 at 14:20

score 0 · Accepted Answer · answered Apr 08 '16 at 14:38

0

try with below code

    N = Cells(Rows.Count, "B").End(xlUp).Row
    For i = 1 To N
        v1 = Cells(i, 2).Value
        v3 = Cells(i, 3).Value
        If v1 = "SWI" And v3 = "IN" Then Cells(i, 2).Value = "Switch Subscription"
        If v1 = "SWI" And v3 = "OUT" Then Cells(i, 2).Value = "Switch Redemption"
    Next i

answered Apr 08 '16 at 14:38

Karthick Gunasekaran

2,697
1
15
25

@shinpencil, mark it as this is solution for your problem. – Karthick Gunasekaran Apr 08 '16 at 14:44

score 0 · Answer 2 · answered Apr 08 '16 at 14:38

0

You have two if statements but never added End If Try this

If v1 = "SWI" And v3 = "IN" Then Cells(i, 2).Value = "Switch Subscription"
ElseIf v1 = "SWI" And v3 = "OUT" Then Cells(i, 2).Value = "Switch Redemption"
End If

answered Apr 08 '16 at 14:38

BerticusMaximus

705
5
16

VBA - conditional value replacement

2 Answers2