How to fix the error" Type Mismatch"

[Nelly2015]

I made the changes to the code below a few times. The same error still popped out. No idea where it went wrong. Need help with identifying the error.
I intend to base on categories stored in Column AB from Row 2 onwards to define division in Column AA.
For next is used to loop through the entire column from Row 2 until the last row.

Sub Division()
    Dim i As Long
    Dim FinalRow As Long
    ActiveWorkbook.Sheets("2020").Range("AB2").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To FinalRow Step 1
        Select Case Range("AB2:AB" & i).Value
        Case "Hair Care"
        Range("AA2:AA" & i).Value = "PCD"
        Case "Oral Care"
        Range("AA2:AA" & i).Value = "PCD"
        Case "Health Care"
        Range("AA2:AA" & i).Value = "PCD"
        Case "Personal Hygiene"
        Range("AA2:AA" & i).Value = "PCD"
        Case "Face Care"
        Range("AA2:AA" & i).Value = "PCD"
        Case "Baby Care"
        Range("AA2:AA" & i).Value = "PCD"
        Case "Body Moisturisers"
        Range("AA2:AA" & i).Value = "PCD"
        Case "Lip Care"
        Range("AA2:AA" & i).Value = "PCD"
        Case "Formulations"
        Range("AA2:AA" & i).Value = "Pharma"
        Case "Pure Herbs-Others"
        Range("AA2:AA" & i).Value = "Pharma"
        Case "Cats/Dogs"
        Range("AA2:AA" & i).Value = "AHP"
        Case "Poultry"
        Range("AA2:AA" & i).Value = "AHP"
        Case "Large Animals"
        Range("AA2:AA" & i).Value = "AHP"
        Case "#N/A"
        Range("AA2:AA" & i).Value = "#N/A"
        End Select
    Next i
End Sub

 

[RoryA]

Your code is comparing multiple cells ranges to a single value, which won't work. It shouldn't be doing that since you are looping anyway. You could use:

Sub Division()
    Dim i As Long
    Dim FinalRow As Long
    ActiveWorkbook.Sheets("2020").Range("AB2").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To FinalRow Step 1
       If IsError(Range("AB" & i).Value) then
         Range("AA" & i).Value = "#N/A"
       Else
        Select Case Range("AB" & i).Value
        Case "Hair Care", "Oral Care", "Health Care", "Personal Hygiene", "Face Care", "Baby Care", "Body Moisturisers", "Lip Care"
           Range("AA" & i).Value = "PCD"
        Case "Formulations", "Pure Herbs-Others"
           Range("AA" & i).Value = "Pharma"
        Case "Cats/Dogs", "Poultry", "Large Animals"
           Range("AA" & i).Value = "AHP"
        Case "#N/A"
           Range("AA" & i).Value = "#N/A"
        End Select
      End If
    Next i
End Sub

If you have a lot of data, using an array would be faster. You could also use a formula for this with a lookup table.

 

[Nelly2015]

Your code is comparing multiple cells ranges to a single value, which won't work. It shouldn't be doing that since you are looping anyway. You could use:

Sub Division()
    Dim i As Long
    Dim FinalRow As Long
    ActiveWorkbook.Sheets("2020").Range("AB2").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To FinalRow Step 1
       If IsError(Range("AB" & i).Value) then
         Range("AA" & i).Value = "#N/A"
       Else
        Select Case Range("AB" & i).Value
        Case "Hair Care", "Oral Care", "Health Care", "Personal Hygiene", "Face Care", "Baby Care", "Body Moisturisers", "Lip Care"
           Range("AA" & i).Value = "PCD"
        Case "Formulations", "Pure Herbs-Others"
           Range("AA" & i).Value = "Pharma"
        Case "Cats/Dogs", "Poultry", "Large Animals"
           Range("AA" & i).Value = "AHP"
        Case "#N/A"
           Range("AA" & i).Value = "#N/A"
        End Select
      End If
    Next i
End Sub

If you have a lot of data, using an array would be faster. You could also use a formula for this with a lookup table.

Thanks for your quick reply!

1. You said "multiple cells ranges to a single value". I don't understand it. Actually it is if then logic. I didn't compare multiple values against one single value. Please clarify it. I need to understand to avoid the same mistake in future.
2. What is an array mean ?

 

[RoryA]

You had this:

Select Case Range("AB2:AB" & i).Value

When i is greater than 2, you are checking the value of more than one cell which means you get an array of values. You cannot compare an array of values against one value using =.

For example, let's say i = 3. your code line is then effectively:

Select Case Range("AB2:AB3").Value

Since that's 2 cells, you get an array with 2 values in it.

The whole point of your loop is to check each cell one at a time, so you need to remove the "AB2:" part.

 

[Nelly2015]

You had this:

Select Case Range("AB2:AB" & i).Value

When i is greater than 2, you are checking the value of more than one cell which means you get an array of values. You cannot compare an array of values against one value using =.

For example, let's say i = 3. your code line is then effectively:

Select Case Range("AB2:AB3").Value

Since that's 2 cells, you get an array with 2 values in it.

The whole point of your loop is to check each cell one at a time, so you need to remove the "AB2:" part.

Thanks a lot. I have got your point.
By the way, I know I can use the vlookup table to do it. I wanted to try a different way to learn more about VBA.