worksheet evaluate function showing zero value

[vivek pare]

>>> as written many times <<<
>>> use code - tags <<<

dim xmax as variant
Set wks2 = Sheets("STATMT")
lastLine = wks1.UsedRange.Rows.Count
XLST = wks2.UsedRange.Rows.Count
For I = 2 To lastLine
Set wks1 = Sheets("KCC")
acno = wks1.Cells(I, 1).Value
Set wks2 = Sheets("STATMT")
wks1.Activate
acno = wks1.Cells(I, 1).Value
wks2.Activate
'wks2.Range
rngVal = Sheets("STATMT").Range("c2:c" & XLST).Address
rngName = Sheets("STATMT").Range("a2:a" & XLST).Address
'Worksheet function MAX returns the largest value in a range
xmax = Evaluate("MAX(IF(" & rngName & "=" & Trim(acno) & "," & rngVal & "))")
wks1.Cells(I, 9).Value = xmax
Next I
End Sub

after running the code i got value "0" in kcc excel sheet column no 9
Can any one solve i have attached kcc and statmt file also
vivek

 

[p45cal]

Sub what()
Dim xmax As Variant
Set wks1 = Sheets("KCC")
Set wks2 = Sheets("STATMT")
lastLine = wks1.UsedRange.Rows.Count
XLST = wks2.UsedRange.Rows.Count
For I = 2 To lastLine
  acno = wks1.Cells(I, 1).Address(external:=True)
  rngVal = wks2.Range("c2:c" & XLST).Address(external:=True)
  rngName = wks2.Range("a2:a" & XLST).Address(external:=True)
  xmax = Evaluate("MAX(IF(" & rngName & "=TRIM(" & acno & ")," & rngVal & "))")
  wks1.Cells(I, 9).Value = xmax
Next I
End Sub

 

[vivek pare]

Thank you the code running excellent
But what is address external true when it is to be applied

 

[p45cal]

See https://docs.microsoft.com/en-us/office/vba/api/excel.range.address?f1url=?appId=Dev11IDEF1&l=en-US&k=k(vbaxl10.chm144076);k(TargetFrameworkMoniker-Office.Version=v16)&rd=true
rngVal = wks2.Range("c2:c" & XLST).Address
returns:
$C$2:$C$16
whereas
rngVal = wks2.Range("c2:c" & XLST).Address(external:=True)
returns:
'[New Microsoft Excel Worksheet (2).xlsx]STATMT'!$C$2:$C$16