VBA Code

shreyanshshah2

New Member
Joined
Mar 18, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hi Guys,
I am trying to use iif function, sum function and countifs function all together in a vba code. I want the following formula as my code

"=SUM(IF($E$2:$E$15=E2,IF(G2>$G$2:$G$15,1/COUNTIFS($E$2:$E$15,A2,$G$2:$G$15,$G$2:$G$15))))+1"

I am using this formula to rank column G based on the criteria range column E.

now my range is not limited to 15. it can vary everytime. Hence I have defined lastrow of the data as Long. Please see the below Code.


Option Explicit

Sub Update_Rank()

Dim GoalsWs As Worksheet
Dim GoalsLastRow As Long
Dim x As Long
Dim I1 As Range, I2 As Range

Set GoalsWs = ThisWorkbook.Worksheets("Output")

GoalsLastRow = GoalsWs.Range("A" & Rows.count).End(xlUp).Row


Set I1 = GoalsWs.Range("E2:E" & GoalsLastRow)
Set I2 = GoalsWs.Range("G2:G" & GoalsLastRow)


'=SUM(IF($A$2:$A$15=A2,IF(B2>$B$2:$B$15,1/COUNTIFS($A$2:$A$15,A2,$B$2:$B$15,$B$2:$B$15))))+1

For x = 2 To GoalsLastRow

GoalsWs.Range("H" & x).Value = Application.WorksheetFunction.Sum( _
IIf(I1 = GoalsWs.Range("E" & x), IIf(GoalsWs.Range("G" & x) > I2, 1 / Application.CountIfs( _
I1, GoalsWs.Range("E" & x), I2, I2), ""), "")) + 1

Next x


End Sub


I am getting a type Mismatch Error. can anyone let me know where I am going wrong.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
These formulas have different cell references:
Excel Formula:
=SUM(IF($E$2:$E$15=E2,IF(G2>$G$2:$G$15,1/COUNTIFS($E$2:$E$15,A2,$G$2:$G$15,$G$2:$G$15))))+1
Capture1.PNG

Excel Formula:
=SUM(IF($A$2:$A$15=A2,IF(B2>$B$2:$B$15,1/COUNTIFS($A$2:$A$15,A2,$B$2:$B$15,$B$2:$B$15))))+1
Capture2.PNG


Which of these is the correct one?
 
Upvote 0
If it's the former, try:
VBA Code:
Sub Update_Rank_Mod()
    Dim lr As Long, r1 As String, r2 As String, i As Long

    With Sheets("Output")
 
        lr = .Range("A" & Rows.Count).End(xlUp).Row
        r1 = .Range("E2:E" & lr).Address
        r2 = .Range("G2:G" & lr).Address

        For i = 2 To lr
            .Cells(i, "H").Value = "=SUM(IF(" & r1 & "=" & "E" & i & ",IF(G" & i & ">" & r2 & ",1/COUNTIFS(" & r1 & ",A" & i & "," & r2 & "," & r2 & "))))+1"
            '=SUM(IF($E$2:$E$15=E2,IF(G2>$G$2:$G$15,1/COUNTIFS($E$2:$E$15,A2,$G$2:$G$15,$G$2:$G$15))))+1
        Next i
 
    End With

End Sub
The type mismatch you're going through is because you use range objects to define cell addresses.
Range objects, if their properties aren't specified, return values in the relevant ranges, not cell addresses.
 
Last edited:
Upvote 0
If it's the former, try:
VBA Code:
Sub Update_Rank_Mod()
    Dim lr As Long, r1 As String, r2 As String, i As Long

    With Sheets("Output")

        lr = .Range("A" & Rows.Count).End(xlUp).Row
        r1 = .Range("E2:E" & lr).Address
        r2 = .Range("G2:G" & lr).Address

        For i = 2 To lr
            .Cells(i, "H").Value = "=SUM(IF(" & r1 & "=" & "E" & i & ",IF(G" & i & ">" & r2 & ",1/COUNTIFS(" & r1 & ",A" & i & "," & r2 & "," & r2 & "))))+1"
            '=SUM(IF($E$2:$E$15=E2,IF(G2>$G$2:$G$15,1/COUNTIFS($E$2:$E$15,A2,$G$2:$G$15,$G$2:$G$15))))+1
        Next i

    End With

End Sub
The type mismatch you're going through is because you use range objects to define cell addresses.
Range objects, if their properties aren't specified, return values in the relevant ranges, not cell addresses.
If it's the former, try:
VBA Code:
Sub Update_Rank_Mod()
    Dim lr As Long, r1 As String, r2 As String, i As Long

    With Sheets("Output")

        lr = .Range("A" & Rows.Count).End(xlUp).Row
        r1 = .Range("E2:E" & lr).Address
        r2 = .Range("G2:G" & lr).Address

        For i = 2 To lr
            .Cells(i, "H").Value = "=SUM(IF(" & r1 & "=" & "E" & i & ",IF(G" & i & ">" & r2 & ",1/COUNTIFS(" & r1 & ",A" & i & "," & r2 & "," & r2 & "))))+1"
            '=SUM(IF($E$2:$E$15=E2,IF(G2>$G$2:$G$15,1/COUNTIFS($E$2:$E$15,A2,$G$2:$G$15,$G$2:$G$15))))+1
        Next i

    End With

End Sub
The type mismatch you're going through is because you use range objects to define cell addresses.
Range objects, if their properties aren't specified, return values in the relevant ranges, not cell addresses.
Thank you Kanadaa. My formula had a Typo. Below is the correct formula.
=SUM(IF($A$2:$A$15=A2,IF(B2>$B$2:$B$15,1/COUNTIFS($A$2:$A$15,A2,$B$2:$B$15,$B$2:$B$15),""),""))+1

This is how my Data looks like incase it helps.

CriteriaValueDesired Rank
10-900002
2​
10-900001
1​
20-300007
4​
20-300005
2​
20-300003
1​
20-300006
3​
48-499003
1​
48-499998
5​
48-499029
4​
48-499999
6​
48-499010
3​
48-499010
3​
48-499005
2​
48-499998
5​
 
Upvote 0
VBA Code:
Sub Update_Rank_Mod()
    Dim lr As Long, r1 As String, r2 As String, i As Long

    With Sheets("Output")
   
        lr = .Range("A" & Rows.Count).End(xlUp).Row
        r1 = .Range("A2:A" & lr).Address
        r2 = .Range("B2:B" & lr).Address

        For i = 2 To lr
            .Cells(i, "H").Value = "=SUM(IF(" & r1 & "=" & "A" & i & ",IF(B" & i & ">" & r2 & ",1/COUNTIFS(" & r1 & ",A" & i & "," & r2 & "," & r2 & "))))+1"
            '=SUM(IF($A$2:$A$15=A2,IF(B2>$B$2:$B$15,1/COUNTIFS($A$2:$A$15,A2,$B$2:$B$15,$B$2:$B$15),""),""))+1
        Next i
        .Range(Cells(lr + 1, "H"), Cells(Rows.Count, "H")).ClearContents
   
    End With

End Sub
 
Upvote 0
VBA Code:
Sub Update_Rank_Mod()
    Dim lr As Long, r1 As String, r2 As String, i As Long

    With Sheets("Output")
  
        lr = .Range("A" & Rows.Count).End(xlUp).Row
        r1 = .Range("A2:A" & lr).Address
        r2 = .Range("B2:B" & lr).Address

        For i = 2 To lr
            .Cells(i, "H").Value = "=SUM(IF(" & r1 & "=" & "A" & i & ",IF(B" & i & ">" & r2 & ",1/COUNTIFS(" & r1 & ",A" & i & "," & r2 & "," & r2 & "))))+1"
            '=SUM(IF($A$2:$A$15=A2,IF(B2>$B$2:$B$15,1/COUNTIFS($A$2:$A$15,A2,$B$2:$B$15,$B$2:$B$15),""),""))+1
        Next i
        .Range(Cells(lr + 1, "H"), Cells(Rows.Count, "H")).ClearContents
  
    End With

End Sub
the Formula works. fine without error. after running the marco, my cell is populated with this formula =SUM(IF(@$A$2:$A$15=A2,IF(B2>@$B$2:$B$15,1/COUNTIFS($A$2:$A$15,A2,$B$2:$B$15,@$B$2:$B$15))))+1

Can we get rid of this @. because of this. the out put generated is not correct. it is ranking each and every cell as 1 only
 
Upvote 0
As for the @ operator, see Implicit intersection operator: @.
I guess it's possible to get rid of it but are you sure the calculation isn't being done the way you want it to because of the operator?
I'm asking this because as the website says, "your formulas will continue to calculate the same way they always have".
 
Upvote 0
As for the @ operator, see Implicit intersection operator: @.
I guess it's possible to get rid of it but are you sure the calculation isn't being done the way you want it to because of the operator?
I'm asking this because as the website says, "your formulas will continue to calculate the same way they always have".
yes, it is not giving me the desired out come. see below column Rank After VBA

CriteriaValueDesired RankRank after VBA
10-900002
2​
1​
10-900001
1​
1​
20-300007
4​
1​
20-300005
2​
1​
20-300003
1​
1​
20-300006
3​
1​
48-499003
1​
1​
48-499998
5​
1​
48-499029
4​
1​
48-499999
6​
1​
48-499010
3​
1​
48-499010
3​
1​
48-499005
2​
1​
48-499998
5​
1​
 
Upvote 0
How about this one?
VBA Code:
Sub Update_Rank_Mod()
    Dim lr As Long, r1 As String, r2 As String, i As Long, ftemp As String

    With Sheets("Output")
    
        lr = .Range("A" & Rows.Count).End(xlUp).Row
        r1 = .Range("A2:A" & lr).Address
        r2 = .Range("B2:B" & lr).Address

        For i = 2 To lr
            .Cells(i, "H").Formula2 = "=SUM(IF(" & r1 & "=" & "A" & i & ",IF(B" & i & ">" & r2 & ",1/COUNTIFS(" & r1 & ",A" & i & "," & r2 & "," & r2 & "))))+1"
        Next i
        .Range(Cells(lr + 1, "H"), Cells(Rows.Count, "H")).ClearContents
    
    End With

End Sub
 
Upvote 0
Solution
this works. Thanks a lot. :)
How about this one?
VBA Code:
Sub Update_Rank_Mod()
    Dim lr As Long, r1 As String, r2 As String, i As Long, ftemp As String

    With Sheets("Output")
   
        lr = .Range("A" & Rows.Count).End(xlUp).Row
        r1 = .Range("A2:A" & lr).Address
        r2 = .Range("B2:B" & lr).Address

        For i = 2 To lr
            .Cells(i, "H").Formula2 = "=SUM(IF(" & r1 & "=" & "A" & i & ",IF(B" & i & ">" & r2 & ",1/COUNTIFS(" & r1 & ",A" & i & "," & r2 & "," & r2 & "))))+1"
        Next i
        .Range(Cells(lr + 1, "H"), Cells(Rows.Count, "H")).ClearContents
   
    End With

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

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