I am trying to create a VBA code where the following occurs:

  1. If column F says "Anna" then column C will say "Apples"
  2. If column F says "Mike","Jake","Dan","Angie" then column C will say "Banana"
  3. If column F says "Molly" then column C will say "Orange"
  4. If column F says "Tony", "Kevin" then column C will say "Kiwi"

2 Answers

0
Community On Best Solutions

You could try

Sub fruit()

Dim name As String
Dim lastrow As Long
Dim i As Long

lastrow = Cells(Rows.Count, 6).End(xlUp).Row

For i = 1 To lastrow

    name = Cells(i, 6).Value
    Select Case name

    Case "Anna"
        Cells(i, 3).Value = "Apples"

    Case "Mike", "Jake", "Dan", "Angie"
        Cells(i, 3).Value = "Banana"

    Case "Molly"
        Cells(i, 3).Value = "Orange"

    Case "Tony", "Kevin"
        Cells(i, 3).Value = "Kiwi"

    Case Else
        Cells(i, 3).Value = "not defined"

    End Select

Next i

End Sub
0
Error 1004 On

You could try:

Option Explicit

Sub test()

    With ThisWorkbook.Worksheets("Sheet1")

        If .Range("F1").Value = "Anna" Then
            .Range("C1").Value = "Apples"
        ElseIf .Range("F1").Value = "Mike" Or .Range("F1").Value = "Jake" Or .Range("F1").Value = "Dan" Or .Range("F1").Value = "Angie" Then
            .Range("C1").Value = "Banana"
        ElseIf .Range("F1").Value = "Molly" Then
            .Range("C1").Value = "Orange"
        ElseIf .Range("F1").Value = "Tony" Or .Range("F1").Value = "Kevin" Then
            .Range("C1").Value = "Kiwi"
        End If

    End With

End Sub