evaluate function showing zero value

Vivek pare

New Member
Joined
Apr 8, 2020
Messages
31
Office Version
  1. 2016
Platform
  1. Windows
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
vivek
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
I have tidied the code up a little see if this works for you:-
One of the key changes for where you were at was to use Address(,,,1) which includes sheet name in the address reference. (Thank you Fluff).
Your sheet activates weren't doing anything and were removed..
The Set Commands didn't need to be done on each iteration of the loop and taken out of the loop.
PS: I didn't know if your acno was text or a number so I used variant.

One other thing, if it doesn't work for you then please check if you have the function MAXIFS available to you.
It is possible that the "MAX(IF" you were using worked on my 365 but doesn't work on your 2016 version.

VBA Code:
Sub MaxCalc()

    Dim xmax As Long    ' XXX Changed to Long
   
    ' XXX Added
    Dim wks1 As Worksheet
    Dim wks2 As Worksheet
    Dim lastLine As Long, XLST As Long, I As Long
    Dim acno As Variant
    Dim rngVal As String, rngName As String
   
    Set wks1 = Sheets("KCC")        ' XXX Moved outside of Loop
    Set wks2 = Sheets("STATMT")
    lastLine = wks1.UsedRange.Rows.Count
    XLST = wks2.UsedRange.Rows.Count
   
    For I = 2 To lastLine
        acno = wks1.Cells(I, 1).Value
        rngVal = wks2.Range("c2:c" & XLST).Address(, , , 1)
        rngName = wks2.Range("a2:a" & XLST).Address(, , , 1)
        '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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top