Problem with VBA formula

[DizzyBear]

Hi! I am encountering a Compile error: Syntax error[FONT=&quot] [/FONT]with the VBA coding.

I would like to refer to column "Q" and produce the result of "CMS/DMS/SMS" in column "AX".

Do anyone know how can I fill up the formula to the last row?

The formula is

=IF(SUMPRODUCT(--ISNUMBER(SEARCH({"CDS","CNM","CMF","CSV"},Q2)))>0,"CMS",IF(SUMPRODUCT(--ISNUMBER(SEARCH({"SPM","SSV","SNM","SMF"},Q2)))>0,"SMS",IF(SUMPRODUCT(--ISNUMBER(SEARCH({"DPM","DSV","DNM","DMF"},Q2)))>0,"DMS",IF(SUMPRODUCT(--ISNUMBER(SEARCH({"KCD"},Q2)))>0,"CMS","SMS"))))





Below is the hardcoding I managed to come up with.



Sub Classification()
'
' Classification Macro
' SMS, DMS, CMS
'
' Keyboard Shortcut: Ctrl+Shift+M
'


Dim sht As Worksheet
Dim lastrow As Long
Set sht = ThisWorkbook.Worksheets("Sheet1")
lastrow = sht.Cells(sht.Rows.Count, "Q").End(xlUp).Row


Columns("AW:AW").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("AX1").Select
ActiveCell.FormulaR1C1 = "Macro"

Range("AX2:AX" & lastrow).Formula = "=IF(SUMPRODUCT(--ISNUMBER(SEARCH({"CDS","CNM","CMF","CSV"},Q2)))>0,"CMS",IF(SUMPRODUCT(--ISNUMBER(SEARCH({"SPM","SSV","SNM","SMF"},Q2)))>0,"SMS",IF(SUMPRODUCT(--ISNUMBER(SEARCH({"DPM","DSV","DNM","DMF"},Q2)))>0,"DMS",IF(SUMPRODUCT(--ISNUMBER(SEARCH({"KCD"},Q2)))>0,"CMS","SMS"))))"


End Sub

 

[DizzyBear]

It is only showing Q2 and Q3
(Not showing the first line "Macro" and the rest)

 

[DizzyBear]

Wow! Perfect!

You make the perfect teacher!

Thanks a lot!