Help with my Macro to Re-Code Data

[JADownie]

I wrote this macor below which looks at the value in column P, and then re-codes the value to something else in the same row in column T.

Ex. If P2 = Apple then T2 = Fruit

It worked somewhat - but it stopped after the first case below "Internet - Access Panel" How would I fix this code to run through every scenario below and not just stop after the first?

Thanks in advance for any input and assistance!

Sub New_Methodology_Coding()


Dim lastrow As Long, t As Long
lastrow = ActiveSheet.UsedRange.Rows.Count
For t = lastrow To 1 Step -1
If Cells(t, 1).Value <> "" Then
If Cells(t, 16).Value = "Internet - Access Panel" Then Cells(t, 20).Value = "Online"
End If
If Cells(t, 1).Value <> "" Then
If Cells(t, 16).Value = "Telephone - Consumer" Then Cells(t, 26).Value = "Telephone"
End If
If Cells(t, 1).Value <> "" Then
If Cells(t, 16).Value = "Telephone - B2B C-Level" Then Cells(t, 26).Value = "Telephone"
End If
If Cells(t, 1).Value <> "" Then
If Cells(t, 16).Value = "Internet - Other / client sup" Then Cells(t, 26).Value = "Online"
End If
If Cells(t, 1).Value <> "" Then
If Cells(t, 16).Value = "N/A" Then Cells(t, 26).Value = "Not Specified"
End If
If Cells(t, 1).Value <> "" Then
If Cells(t, 16).Value = "Analysis" Then Cells(t, 26).Value = "Analysis"
End If

If Cells(t, 1).Value <> "" Then
If Cells(t, 16).Value = "Face-to-Face (Quantitative)" Then Cells(t, 26).Value = "F2F (Quant)"
End If
If Cells(t, 1).Value <> "" Then
If Cells(t, 16).Value = "Telephone - B2B non C-Level" Then Cells(t, 26).Value = "Telephone"
End If
 If Cells(t, 1).Value <> "" Then
If Cells(t, 16).Value = "Qualitative" Then Cells(t, 26).Value = "F2F (Qual)"
End If
If Cells(t, 1).Value <> "" Then
If Cells(t, 16).Value = "Presentation" Then Cells(t, 26).Value = "Presentation"
End If
If Cells(t, 1).Value <> "" Then
If Cells(t, 16).Value = "Telephone" Then Cells(t, 26).Value = "Telephone"
End If
If Cells(t, 1).Value <> "" Then
If Cells(t, 16).Value = "QUANtitative - Face-to-Face" Then Cells(t, 26).Value = "F2F (Quant)"
End If
If Cells(t, 1).Value <> "" Then
If Cells(t, 16).Value = "Internet - External Access Pan" Then Cells(t, 26).Value = "Online"
End If
If Cells(t, 1).Value <> "" Then
If Cells(t, 16).Value = "QUALitative - Face-to-Face" Then Cells(t, 26).Value = "F2F (Qual)"
End If

If Cells(t, 1).Value <> "" Then
If Cells(t, 16).Value = "Mail" Then Cells(t, 26).Value = "Mail"
End If
If Cells(t, 1).Value <> "" Then
If Cells(t, 16).Value = "Face-to-Face (Qualitative)" Then Cells(t, 26).Value = "F2F (Qual)"
End If
If Cells(t, 1).Value <> "" Then
If Cells(t, 16).Value = "NULL" Then Cells(t, 26).Value = "Not Specified"
End If

           
Next t
        
End Sub

 

[StevenD]

Hi,

I would be inclined not to use the if's but to use Select instead.

Try this.
Just fill in the rest as required.

Dim lastrow As Long, t As Long
lastrow = ActiveSheet.UsedRange.Rows.Count
For t = lastrow To 1 Step -1
    If Cells(t, 1).Value <> "" Then
        Select Case Cells(t, 16).Value
            Case "Internet - Access Panel"
                Cells(t, 20).Value = "Online"
            Case "Telephone - Consumer"
                Cells(t, 26).Value = "Telephone"

            'PUT THE REST OF THE CASE STATEMENTS HERE

        End Select
    End If
Next t

HTH

 

[Von Pookie]

It worked somewhat - but it stopped after the first case below "Internet - Access Panel" How would I fix this code to run through every scenario below and not just stop after the first?

Why? The cell can only contain one of those entries--why check for the rest of them when you find the matching one? Technically speaking, the code you have posted *is* looping through the rest of those, due to the way you have written the IF statements. It doesn't need to, though. Once it finds the match, it doesn't need to check the rest.

That being said, you could rewrite the loop so it may be a little faster:

For t = lastrow To 1 Step -1
    If Cells(t, 1).Value <> "" Then
        Select Case Cells(t, 16)
            Case Is = "Internet - Access Panel": Cells(t, 20).Value = "Online"
            Case Is = "Telephone - Consumer": Cells(t, 26).Value = "Telephone"
            Case Is = "Telephone - B2B C-Level": Cells(t, 26).Value = "Telephone"
            Case Is = "Internet - Other / client sup": Cells(t, 26).Value = "Online"
            Case Is = "N/A": Cells(t, 26).Value = "Not Specified"
            Case Is = "Analysis": Cells(t, 26).Value = "Analysis"
            Case Is = "Face-to-Face (Quantitative)": Cells(t, 26).Value = "F2F (Quant)"
            Case Is = "Telephone - B2B non C-Level": Cells(t, 26).Value = "Telephone"
            Case Is = "Qualitative": Cells(t, 26).Value = "F2F (Qual)"
            Case Is = "Presentation": Cells(t, 26).Value = "Presentation"
            Case Is = "Telephone": Cells(t, 26).Value = "Telephone"
            Case Is = "QUANtitative - Face-to-Face": Cells(t, 26).Value = "F2F (Quant)"
            Case Is = "Internet - External Access Pan": Cells(t, 26).Value = "Online"
            Case Is = "QUALitative - Face-to-Face": Cells(t, 26).Value = "F2F (Qual)"
            Case Is = "Mail": Cells(t, 26).Value = "Mail"
            Case Is = "Face-to-Face (Qualitative)": Cells(t, 26).Value = "F2F (Qual)"
            Case Is = "NULL": Cells(t, 26).Value = "Not Specified"
        End Select
    End If
Next t

 

[lenze]

Ditto Steve's suggestion. Since you only have 1 line of code for each case, you can save space like this

Case "Internet - Access Panel": Cells(t, 20).Value = "Online"
Case "Telephone - Consumer": Cells(t, 26).Value = "Telephone"
'etc

Also, is

Cells(t , 26)

correct, as this refers to column "Z"
lenze