shreyanshshah2
New Member
 Joined
 Mar 18, 2021
 Messages
 7
 Office Version

 365
 Platform

 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.
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.