• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Please on Rank and Countif function

Shailender

Shailender

Member
Hello All,

I would like to write a macro by using Rank function and counif function. I wrote the macro,
but it is throwing the error message. I want the formula to be placed in cell C1.

Please help me out, as i am attaching the excel sheet for your reference.

Thank you!

Regards,
Shailender.
 

Attachments

  • Question2.xlsm
    16.2 KB · Views: 6
Hi !

Just respecting Excel model object :​
Code: 
Sub Demo1()
    With Sheet1.Range("A2", Sheet1.Cells(Rows.Count, 1).End(xlUp))
        .Offset(, 1).Formula = Replace("=IF(A2=0,"""",RANK(A2,#)+COUNTIF(#,A2)-1)", "#", .Address)
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !
​
 
A variant :​
Code: 
Sub Demo2()
    With Sheet1.Range("A2", Sheet1.Cells(1).End(xlDown))
        .Offset(, 1).Formula = Replace("=IF(A2=0,"""",RANK(A2,#)+COUNTIF(#,A2)-1)", "#", .Address)
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !
​
 
Marc, thank you for your prompt response, but the code isn't working it is throwing an error message at the .Address saying that
Complie error: Invalid or Unqualified error.
Marc, is it possible to provide a code in such a way that i have attached in the excel in Module1..?
Once again thank you!
 
Narayank991, thank you so much for the solution it is working, since i have been trying this line of code from one month, now it got resolved.

And i also want to thank Marc L for his prompt response.
 
Narayank91, Could you please help me out with another function which i want to use, similar to the earlier one but this time i would like to use IF & Sum function in that. I wrote the macro, but it is throwing the error message. I want the formula to be placed in cell A1.

Please help me out, as i am attaching the excel sheet for your reference and also provide a code in such a way that i have attached in the excel in Module1.

Please refer to the B2 cell.

Thank you in advance.
 

Attachments

  • Question2.xlsm
    18.2 KB · Views: 2
Narayan, you are genius, it is working fine. Quick question on the formula Range("A3").Formula = "=IF(SUM(" & StrColm & ":" & Endcolm & ")" & "=0 , " & """" & """" & " , Sum(" & StrColm & ":" & Endcolm & "))", why did you used " & """"&"""" after 0, Could you please explain me about this. thank you in advance.
 
Hi ,

The formula you want entered is :

=IF(SUM(A2:L2)=0 , "" , SUM(A2:L2))

The variables are the highlighted references ; in your code these are available in the VBA variables StrColm and EndColm.

Because these are variables whose values need to be used in the formula string , they need to be concatenated with the rest of the formula string.

The double quotes each need to be entered as """" when they are outside of the formula string , and because there are two of them , they need to be concatenated using the & symbol.

Thus the overall VBA formula string will be :

"=IF(SUM(" & StrColm & ":" & Endcolm & ")" & "=0 , " & """" & """" & " , Sum(" & StrColm & ":" & Endcolm & "))"

If the two double quotes are within the overall formula string , then each double quote is represented by two double quotes , and the overall formula string will be :

"=IF(SUM(" & StrColm & ":" & Endcolm & ")" & "=0 , """" , Sum(" & StrColm & ":" & Endcolm & "))"

Narayan
 
Narayan, thank you for your reply. This is very informative. Learnt some new things in the process. Thank you once again. Regards. Shailender.
 
You must log in or register to reply here.
Back
Top