VBA code for if statement referencing to a list

[NoxGalleon106]

Hi,

Can someone help me on the coding of the below condition? basically, ActiveCell.Offset(0, 25).Value should be equal to "Asia" if the activecell in column F contains the below list and ActiveCell.Offset(0, 25) is blank.

list: (note that the text can have XXX at the end of the word, so it is more like a contains condition)
HK
SG
CN
AU
JP


If ActiveCell.Value =list And ActiveCell.Offset(0, 25).Value = "" Then
ActiveCell.Offset(0, 25).Value = "Asia"

 

[Fluff]

How about

Sub IfInList()

    Dim Cnt As Long
    Dim Arr As Variant
    
    Arr = Array("HK", "SG", "CN", "AU", "JP")
    
    With ActiveCell
        If UBound(Filter(Arr, .Value, False, vbTextCompare)) >= 0 _
            And Len(.Offset(, 25).Value) = 0 Then
            .Offset(, 25).Value = "Asia"
        End If
    End With

End Sub

 

[NoxGalleon106]

Hi,

Thanks for your help but it is currently not working. All the cells are having "Asia" even if the active cell does not contain those values in array.
It should only show as "Asia" if it contains the values in array and the activecell.offset(0,25).value = ""

 

[Fluff]

Apologies, it should be

Sub IfInList()

    Dim Cnt As Long
    Dim Arr As Variant
    
    Arr = Array("AU", "CN", "HK", "JP", "SG")
    
    With ActiveCell
        If UBound(Filter(Arr, Left(.Value, 2), , vbTextCompare)) >= 0 _
            And Len(.Offset(, 25).Value) = 0 Then
            .Offset(, 25).Value = "Asia"
        End If
    End With

End Sub