CODEWITHSUNDEEP
vbaexcel
Adrian
Adrian

Reputation: 907

How can I improve my "if and else if" VBA? code

I am trying to understand the if and else if in VBA but my code doesn't work. Could you advise what I am doing wrong?

Sub columnA()
Dim r As Range
Dim src As Worksheet
Dim copyRange As Range
Dim pasteRange As Range
Set src = ThisWorkbook.Sheets("report")

lastRow = src.Range("D" & src.Rows.Count).End(xlUp).Row
Set copyRange = src.Range("D3:D" & lastRow)
Set pasteRange = src.Range("A3:A" & lastRow)

If copyRange = "Updates" Then
pasteRange = "Post-Edit"
ElseIf copyRange = "New Product Translations" Then
pasteRange = "Post-Edit"
ElseIf copyRange = "Misc" Then
pasteRange = "Human"
ElseIf copyRange Is Nothing Then Exit Sub
End If

End Sub

What I want to do is to insert Post-Edit or Human in column A if text in column D matches criteria so if text in column D is Update or New New Product Translations then cell in column A should be Post-Edit, if cell in column D contains Misc then I want to insert Human in column A.

Upvotes: 1

Views: 190

Answers (3)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

You could use a formula rather than If..Else or Select Case.
The formula, if typed directly, would be =IF(OR($D3="Updates",$D3="New Product Translations"),"Post-Edit",IF($D3="Misc","Human","")).

Within your code it would be: 

Sub columnA()

    Dim src As Worksheet
    Dim formulaRange As Range
    Dim lastrow As Long

    Set src = ThisWorkbook.Sheets("report")
    lastrow = src.Range("D" & src.Rows.Count).End(xlUp).Row
    Set formulaRange = src.Range("A3:A" & lastrow)

    formulaRange.FormulaR1C1 = _
        "=IF(OR(RC4=""Updates"",RC4=""New Product Translations""),""Post-Edit"",IF(RC4=""Misc"",""Human"",""""))"

    'Replace formula with values.
    formulaRange = formulaRange.Value

End Sub

If you have the lookups in a table, for example your values to find in H1:H3 and your looked-up values in I1:I3, you could use =IFERROR(VLOOKUP(D3,$H$1:$I$3,2,FALSE),"").

The formula in VBA would be: 

formulaRange.FormulaR1C1 = _
    "=IFERROR(VLOOKUP(RC4,R1C8:R3C9,2,FALSE),"""")"

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33682

You can switch from If to Select Case, see code below:

Select Case copyRange
    Case "Updates", "New Product Translations"
        pasteRange = "Post-Edit"

    Case "Misc"
        pasteRange = "Human"

End Select

Edit 1: Full code edited 

Option Explicit

Sub columnA()

Dim R As Range
Dim src As Worksheet
Dim copyRange As Range
Dim pasteRange As Range
Dim LastRow As Long

Set src = ThisWorkbook.Sheets("report")
With src
    LastRow = .Range("D" & .Rows.Count).End(xlUp).Row
    Set copyRange = .Range("D3:D" & LastRow)
    Set pasteRange = .Range("A3:A" & LastRow)
End With

If Not copyRange Is Nothing Then
    For Each R In copyRange
        Select Case R.Value
            Case "Updates", "New Product Translations"
                R.Offset(, -3).Value = "Post-Edit" ' use offset of 3 columns to put the value in column "A"

            Case "Misc"
                R.Offset(, -3).Value = "Human"

        End Select
    Next R
End If

End Sub

Upvotes: 3

Vityata
Vityata

Reputation: 43585

You can add a boolean condition (or) in the If like this:

If copyRange = "Updates" Or copyRange = "New Product Translations" Then
    pasteRange = "Post-Edit"
ElseIf copyRange = "Misc" Then
    pasteRange = "Human"
ElseIf copyRange Is Nothing Then
    Exit Sub
End If

The above code will work, if the cell range is consisted of one cell. For multiples, you should think of a business logic what exactly do you want to compare. However, if you decide to compare only the first one, this will do the job:

If CopyRange.Cells(1,1) = "Updates"

Make sure to change it everywhere correspondingly. pasteRange = "Something" is ok.

Concerning what you are doing wrong - copyRange cannot be Nothing, you are setting it to a range a bit above. However, its cells can be empty.

To check this, try like following: ElseIf copyRange.Cells(1,1) = vbNullString

Thus you will check the first cell in the range for being empty.

Upvotes: 0

Related Questions