Need help with "Compile error: Expected: named parameter"

[Eloise T]

I receive a regular report for each of my customers but the number of rows will vary based on the amount of business that particular customer has. Each customer has their own tab (worksheet) in the workbook, except for tab #1 which is where I document formula information. For whatever reason (I believe due to frequently appending rows from the regular reports to the tabs), my current CFs seem to mysteriously change the range on their own and I have to go back into the Conditional Formatting to “reset” them back to what they need to be hence the desire and need to automate via a VBA macro.

Each report has the same column headings/titles which use Rows 1 and 2, so the Conditional Formats start in Row 3 (see below). I plan to put a button in the “Formula Info” tab (worksheet #1) that I can use to initiate the macro.

Sub CFsReset()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Formula Info" And ws.Name <> "Sheet2" Then
            If ws.Cells(Rows.Count, 3).End(xlUp).Row > 2 Then
                With ws.[A3:M3].Resize(ws.Cells(Rows.Count, 3).End(xlUp).Row - 2)
'        > > > This VBA changes/resets the Conditional Formats for cell rows ~3 to 5003 in the Columns C, K, L, and M.
'              A3:M3 array tells where to apply change(s).  (Rows.Count, 3) tells in which row to start.
 

                    .FormatConditions.Add Type:=xlExpression, Formula1:"=OR($C3="D70",$C3="E70",$C3="KDL70",$C3="LC70",$C3="LC-70",$C3="M70",$C3="PNC70",$C3="PNL70",$C3="PNR70")"
                    .FormatConditions(1).Interior.ColorIndex = 3    'Red =$C$3:$C$5003

                    .FormatConditions.Add Type:=xlExpression, Formula1:"=OR($C3="M75",$C3="P75",$C3="UN75")"
                    .FormatConditions(1).Interior.ColorIndex = 7    'Magenta =$C$3:$C$5003

                    .FormatConditions.Add Type:=xlExpression, Formula1:"=OR($C3="LC80",$C3="LC-80",$C3="M80",$C3="PNE80",$C3="PNL80")"
                    .FormatConditions(1).Interior.ColorIndex = 33   'Light Blue =$C$3:$C$5003

                    .FormatConditions.Add Type:=xlExpression, Formula1:=OR($C3="RS84")
                    .FormatConditions(1).Interior.ColorIndex = 15   'Brown =$C$3:$C$5003

                    .FormatConditions.Add Type:=xlExpression, Formula1:=OR($C3="DM85")
                    .FormatConditions(1).Interior.ColorIndex = 33   'Violet =$C$3:$C$5003

                    .FormatConditions.Add Type:=xlExpression, Formula1:=OR($C3="LC90")
                    .FormatConditions(1).Font.ColorIndex = 14       'Green
                     .FormatConditions(1).Interior.ColorIndex = 6    'Yellow =$C$3:$C$5003

                    .FormatConditions.Add Type:=xlExpression, Formula1:=OR($F3="REBILLED",$F3="REPAID",$F3="PAID")
                    .FormatConditions(1).Font.ColorIndex = 7        'Magenta
                     .FormatConditions(1).Interior.ColorIndex = 50   'Green =$F$3:$F$5003

                    .FormatConditions.Add Type:=x1Expression, Formula1:=$L3="LEN ≠ 9" 
                    .FormatConditions(1).Interior.ColorIndex = 3    'Red Bold   =$K$3:$K$5003,$M$3:$M$5003


 
                End With
            End If
        End If
    Next
End Sub

 

[r2c2]

@Eloise T When I record a macro, it shows me rules like this:
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$C2>AVERAGE($C$2:$C$5)"

I tested this with new ranges and it seems to work fine.

I suggest using no double quotes inside the formula1: part, unless they are part of the formula logic, in which case you should escape them with another double quote. For example, below rule will become

=$A1="Something"

Formula1:="=$A1=""Something"""

 

[NARAYANK991]

Hi ,

In addition to what Chandoo has pointed out (replace all single quotes by double quotes , except for the quotes on the extreme left and on the extreme right of each formula) , replace all instances of :

Formula1:

by

Formula1:=

Narayan

 

[Eloise T]

@Eloise T When I record a macro, it shows me rules like this:
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$C2>AVERAGE($C$2:$C$5)"

I tested this with new ranges and it seems to work fine.

I suggest using no double quotes inside the formula1: part, unless they are part of the formula logic, in which case you should escape them with another double quote. For example, below rule will become

=$A1="Something"

Formula1:="=$A1=""Something"""

r2c2 and Narayank9991,

Thank you for your replies. Using your suggestions the VBA compiles except for the last CF which is:

=$L3="LEN ≠ 9"

When I paste that line into the macro, it changes the ≠ to a ? and causes the compilation to choke.

Any suggestions?


Also, when I try to RECORD MACRO and set up a Conditional Format, I only get the following (see picture below). It doesn't show any code produced. What am I doing wrong?

 

[NARAYANK991]

Hi ,

The symbol for inequality is <>

Secondly , you cannot have just LEN by itself ; it expects a parameter ; you can have "=LEN($C3) <> 9" , or going by what you have posted :

"=LEN($L3) <> 9"

Narayan

 

[Eloise T]

For some reason VBA still doesn't like that line.

CF thinks it's ok as:
=$L3="LEN ≠ 9"
since it is specifically looking for:
"LEN ≠ 9" in the range $L$3:$L$5003

 

[NARAYANK991]

Hi ,

This is the correct one :

.FormatConditions.Add Type:=xlExpression, Formula1:="=LEN($L3) <> 9"

I suspect your xlExpression is actually x1Expression , where in the second case there is a digit 1 instead of the letter L in lower case.

Narayan

 

[shrivallabha]

Hi ,

This is the correct one :

.FormatConditions.Add Type:=xlExpression, Formula1:="=LEN($L3) <> 9"

I suspect your xlExpression is actually x1Expression , where in the second case there is a digit 1 instead of the letter L in lower case.

Narayan

Why suspect? The proof is there in OP's first post (see posted code), it is "1".

 

[NARAYANK991]

Why suspect? The proof is there in OP's first post (see posted code), it is "1".

Hi ,

Most often , posters do not copy and paste ; there have been many cases where such mistakes are pointed out , and then the OP replies saying that it was a typo when they typed out the formula / line of code.

Narayan

 

[Eloise T]

1. What does OP stand for?

2. Not having any training in VBA (nor Excel for that matter, only what I've gleaned here on Chandoo [my complements to Chandoo]), and not being able to discern between the lower case L and a 1 due to the Times Roman font I was using at the time, I did not know whether it was xlExpression or x1Expression so I did half of the code in "1" and half in lower case L, then compiled to determine which was correct. Having then determined it was supposed to be a lower case L, I missed the last line that needed to be corrected.

3. How do I brute force: =$L3="LEN ≠ 9"
into the macro code as it doesn't like the ≠ sign? (Note: <> won't work here as "LEN ≠ 9" is a text string, not an Excel function.)

Following is the line of code that looks for "LEN ≠ 9"
=IF(A3<>"",IF(OR(LEFT(A3,3)<>"ITI",LEN(A3)<>9),"LEN ≠ 9","-"),"--")

 

[SirJB7]

Hi, Eloise T!

- OP: original poster, person who started a thread.

- whenever faced with an Excel constant between xl and x1 go for xl of Excel.

- what's LEN in that formula? you could use something like:
If Len([L3])<>9 then
and it works.

Regards!

 

[Chihiro]

So, it's a literal text string?

then...
Formula1:="=$L3=""LEN ≠ 9"""

As pointed out previously, you need to replace each double quote with 2 double quote within vba.

 

[Eloise T]

Hi, Eloise T!

- OP: original poster, person who started a thread.

- whenever faced with an Excel constant between xl and x1 go for xl of Excel.

- what's LEN in that formula? you could use something like:
If Len([L3])<>9 then
and it works.

Regards!

 

[Eloise T]

So, it's a literal text string?

then...
Formula1:="=$L3=""LEN ≠ 9"""

As pointed out previously, you need to replace each double quote with 2 double quote within vba.

My problem is VBA, at least for the moment, doesn't like ≠ . It changes it to a question mark....and a question mark won't ever be found by:
=IF(A3<>"",IF(OR(LEFT(A3,3)<>"ITI",LEN(A3)<>9),"LEN ≠ 9","-"),"--")

 

[Eloise T]

Hi, Eloise T!

- OP: original poster, person who started a thread.

- whenever faced with an Excel constant between xl and x1 go for xl of Excel.
Good way to remember!

- what's LEN in that formula? you could use something like:
If Len([L3])<>9 then
and it works.

Regards!

 

[SirJB7]

Hi, Eloise T!
As Chihiro told you above:

Formula1:="=$L3=""LEN ≠ 9"""

As pointed out previously, you need to replace each double quote with 2 double quote within vba.

A trick for persons with ADHD (aka Attention Deficit Hyperactivity Disorder) is this:
a) double all quotes in formula
b) embed a) into quotes.

Regards!

 

[Eloise T]

Hi, Eloise T!
As Chihiro told you above:

A trick for persons with ADHD (aka Attention Deficit Hyperactivity Disorder) is this:
a) double all quotes in formula
b) embed a) into quotes.

Regards!

My dear Sir,
You need to keep up with the times...I am way past that problem. You asked, "What's LEN in that formula?"
I pointed it out with the black arrow....right below your question.

 

[NARAYANK991]

My problem is VBA, at least for the moment, doesn't like ≠ . It changes it to a question mark....and a question mark won't ever be found by:
=IF(A3<>"",IF(OR(LEFT(A3,3)<>"ITI",LEN(A3)<>9),"LEN ≠ 9","-"),"--")

Hi ,

What you post is not possible , unless what you have posted is not the whole story ; only the complete code listing can confirm / clarify this.

The following :

=IF(A3<>"",IF(OR(LEFT(A3,3)<>"ITI",LEN(A3)<>9),"LEN ≠ 9","-"),"--")

is just a worksheet formula , which can be entered as it is , in any worksheet cell ; it will do its job flawlessly.

You can try entering it in a worksheet , and you will see that the cell which has this formula will happily display the text LEN ≠ 9 if the conditions are satisfied.

I cannot imagine VBA protesting , unless the cause is something else , something that is not apparent in what you have posted.

Either post the complete code , or even better upload the workbook with the data and the code in it. Your problem will be solved in minutes.

Narayan

 

[Chihiro]

Ah, then try...
Formula1:="=$L3=""LEN" & ChrW(8800) & "9"""

Edit:
VBA isn't able to directly work with Unicode characters and thus you need ChrW code to represent the character. You won't be able to debug.print, but if you put it into cell or in CF, it should be correctly interpreted as literal string for ≠.

 

[Eloise T]

I had to add a space after LEN and a space in front of 9, otherwise that's sheer genius! Formula1:="=$L3=""LEN " & ChrW(8800) & " 9"""