# VBA Code

#### shreyanshshah2

##### New Member
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

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

##### Active Member
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``

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

Which of these is the correct one?

##### Active Member
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

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

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

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.

 Criteria Value Desired Rank 10-900 002 2​ 10-900 001 1​ 20-300 007 4​ 20-300 005 2​ 20-300 003 1​ 20-300 006 3​ 48-499 003 1​ 48-499 998 5​ 48-499 029 4​ 48-499 999 6​ 48-499 010 3​ 48-499 010 3​ 48-499 005 2​ 48-499 998 5​

##### Active Member

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

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

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

##### Active Member

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

 Criteria Value Desired Rank Rank after VBA 10-900 002 2​ 1​ 10-900 001 1​ 1​ 20-300 007 4​ 1​ 20-300 005 2​ 1​ 20-300 003 1​ 1​ 20-300 006 3​ 1​ 48-499 003 1​ 1​ 48-499 998 5​ 1​ 48-499 029 4​ 1​ 48-499 999 6​ 1​ 48-499 010 3​ 1​ 48-499 010 3​ 1​ 48-499 005 2​ 1​ 48-499 998 5​ 1​

##### Active Member
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

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

#### shreyanshshah2

##### New Member
this works. Thanks a lot.
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

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

Replies
2
Views
80
Replies
1
Views
49
Replies
4
Views
41
Replies
1
Views
79
Replies
3
Views
29

### Forum statistics

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.

### Which adblocker are you using?

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

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