VBA Code

shreyanshshah2

New Member
Joined
Mar 18, 2021
Messages
7
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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
345
Office Version
  1. 365
Platform
  1. Windows
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?
 

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
345
Office Version
  1. 365
Platform
  1. Windows
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:

shreyanshshah2

New Member
Joined
Mar 18, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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​
 

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
345
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

shreyanshshah2

New Member
Joined
Mar 18, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
 

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
345
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

shreyanshshah2

New Member
Joined
Mar 18, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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​
 

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
345
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

shreyanshshah2

New Member
Joined
Mar 18, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,809
Members
416,983
Latest member
LessThanAverageUser

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
Top