Refer to a variable in MAXIF-CodePudding

Sub test()
With Sheets("DATA")
    'test1 ---> cell A1 has a correct result
.Range("A1").FormulaArray = "=MAX(IF(LEFT(C1:C10000,3) = ""PB-"",SUBSTITUTE(C1:C10000,""PB-"","""") 0))"

    'test2 has a correct result
test2 = .[max(if(left(c1:c10000,3)="PB-",substitute(c1:c10000, "PB-" ,"") 0))]

    'test3 gave me an error 2015 in locals window
test3 = Evaluate("max(if(left(c1:c10000,3)=""PB-"",substitute(c1:c10000, ""PB-"" ,"") 0))")
End With
End Sub

What I want :
C1:C10000 is a range coming from set rg = .range("data").columns(2)
"PB-" is a value coming from id = .range("D1").value
Not writing the result to a cell first.

So, before I implement the variable, I want to test it first by writing the code in test3 with evaluate syntax which almost the same like test1. But since the test3 already gave me an Error2015, I can't move on to try to change the code into some variables.

I've also tried """" for the "PB-" substitution in test3, but the result is 0.

My question:

How the code in test3 should be ?
How is the code in test3 (or maybe test2) if using the rg and id variable ?

Your help would be greatly appreciated.
Thank you in advanced.

Sub test()
With Sheets("DATA")
Set rg = .Range("data").Columns(2)
'rg.Select
ID = "PB-"
test3a = rg.Parent.Evaluate("MAX(IF(LEFT(" & rg.Address(0, 0) & ",3) = " & ID & ",SUBSTITUTE(" & rg.Address(0, 0) & "," & ID & ","""") 0))")
test3b = Evaluate("max(if(left(" & rg.Address(0, 0) & ",3)=""" & ID & """,substitute(" & rg.Address(0, 0) & ", """ & ID & """ ,"") 0))")
test3c = Evaluate("max(if(left(" & rg.Address(0, 0) & ",3)=""" & ID & """,substitute(" & rg.Address(0, 0) & ",""" & ID & ""","""") 0))")
End With
End Sub

Sub test3a and test3b give me Error2015, test3c give me 0 result.
The debug.print result of test3c is max(if(left(C1:C12921,3)="PB-",substitute(C1:C12921,"PB-","") 0)) `

CodePudding user response：

Formula vs Shortcut Notation vs Evaluate

Option Explicit

Sub TestInitial()
    
    With ThisWorkbook.Worksheets("DATA")
        
        Dim Test1
        .Range("A1").FormulaArray = "=MAX(IF(LEFT(C1:C10,3)=""" _
            & "PB-"",SUBSTITUTE(C1:C10,""PB-"","""") 0))"
        Test1 = .Range("A1").Value
        Debug.Print "Initial Test1 = " & Test1
        
        Dim Test2
        Test2 = .[MAX(IF(LEFT(C1:C10,3)="PB-",SUBSTITUTE(C1:C10,"PB-","") 0))]
        Debug.Print "Initial Test2 = " & Test2

        Dim Test3
        Test3 = .Evaluate("MAX(IF(LEFT(C1:C10,3)=""PB-""," _
            & "SUBSTITUTE(C1:C10, ""PB-"" ,"""") 0))")
        Debug.Print "Initial Test3 = " & Test3
    
    End With

End Sub

Sub TestFinal()
    
    With ThisWorkbook.Worksheets("DATA")
        
        Dim rgAddress As String
        rgAddress = .Range("data").Columns(2).Address(0, 0)
        ' To exclude the first cell (header), instead you could do:
        'With .Range("data").Columns(2)
        '    rgAddress = .Resize(.Rows.Count - 1).Offset(1).Address(0, 0)
        'End With
        
        Dim ID As String: ID = CStr(.Range("D1").Value)
        
        Dim Test1
        .Range("A1").FormulaArray = "=MAX(IF(LEFT(" & rgAddress & ",3)=""" _
            & ID & """,SUBSTITUTE(" & rgAddress & ",""" & ID & ""","""") 0))"
        Test1 = .Range("A1").Value
        Debug.Print "Final Test1 = " & Test1
        
        Dim Test2
        Test2 = .[MAX(IF(LEFT(data,3)=D1,SUBSTITUTE(data,D1,"") 0))]
        Debug.Print "Final Test2 = " & Test2

        Dim Test3
        Test3 = .Evaluate("MAX(IF(LEFT(" & rgAddress & ",3)=""" _
            & ID & """,SUBSTITUTE(" & rgAddress & ",""" & ID & ""","""") 0))")
        Debug.Print "Final Test3 = " & Test3
    
    End With

End Sub