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

 

[Michael M]

Try

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]

WOW! You are great!

I have been trying to solve this for quite some time!

Thank you for solving it!


I am curious why ""CDS"" is needed?

It is quite interesting that when converting a formula to VBA and additional " is needed.

Once again! Thank you! I really appreciate your help on this matter!

 

[Michael M]

If CDS is not needed remove it, but it was in your original formula....I simply copied the formula !!

Range("AX2:AX" & lastrow).Formula = "=IF(SUMPRODUCT(--ISNUMBER(SEARCH({""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]

Sorry, I meant why did a single amperstand (In Excel Formula) changed to double amperstand when used in the Visual Basic for Application

 

[Michael M]

you mean the quotes...like this ??

""CNM""

Have a read here for a clearer, but lengthy explanation !!


https://msdn.microsoft.com/en-us/vba/access-vba/articles/quotation-marks-in-strings


Put simply....you just do !!

 

[DizzyBear]

May I check if I want to use the macro for Personal.XLSB (Able to be access in all workbook), will there be any changes to the coding?

you mean the quotes...like this ??

""CNM""

Have a read here for a clearer, but lengthy explanation !!


https://msdn.microsoft.com/en-us/vba/access-vba/articles/quotation-marks-in-strings


Put simply....you just do !!

 

[Michael M]

Try it !!
But I can see no reason for it not to work !!

 

[DizzyBear]

Hi! I have tried it but it only show the first 2 line instead of the whole column of result

 

[Norie]

Are you still using this in the code?

Set sht = ThisWorkbook.Worksheets("Sheet1")

If you are then ThisWorkbook will refer to the workbook the code is in, i.e. your personal workbook, so when you are trying to find the last row later in the code you will get an incorrect result.

Try changing ThisWorkbook to ActiveWorkbook in the above code.