Countif and Last row

A

Av8tordude

Well-known Member
Joined
Oct 13, 2007
Messages
1,075
Office Version
  1. 2019
Platform
  1. Windows
When using the first code, the code output the results required. however, when I want to make the code dynamic to find the last row, the output is (0) zero. Can someone explain what I'm doing wrong and offer a solution.

Thank you kindly


Code: 
Dim i As Long

LRow = Range("A" & Rows.Count).End(xlUp).Row
With ActiveSheet
    For i = 16 To .Range("T" & Rows.Count).End(xlUp).Row
        .Range("S" & i) = WorksheetFunction.CountIf(.[B][COLOR=#ff0000]Range("A16:A346"[/COLOR][/B]), Range("T" & i))
    Next i
End With


Find last row....

Code: 
Dim i As LongDim LRow As Long


LRow = Range("A" & Rows.Count).End(xlUp).Row
With ActiveSheet
    For i = 16 To .Range("T" & Rows.Count).End(xlUp).Row
        .Range("S" & i) = WorksheetFunction.CountIf(.[COLOR=#ff0000][B]Range("A16" & LRow[/B][/COLOR]), Range("T" & i))
    Next i
End With
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Try adding a colon:

Code: 
.Range("S" & i) = WorksheetFunction.CountIf(.[COLOR=#ff0000][B]Range("A16[/B][/COLOR][COLOR=#000000][/COLOR][SIZE=3][COLOR=#000000]:[/COLOR][/SIZE][COLOR=#ff0000][B]" & LRow[/B][/COLOR]), Range("T" & i))
 
Upvote 0
Av8tordude said:
Code: 
Dim i As LongDim LRow As Long


LRow = Range("A" & Rows.Count).End(xlUp).Row
With ActiveSheet
    For i = 16 To .Range("T" & Rows.Count).End(xlUp).Row
        .Range("S" & i) = WorksheetFunction.CountIf(.[COLOR=#ff0000][B]Range("A16:A" & LRow[/B][/COLOR]), Range("T" & i))
    Next i
End With
Click to expand...

Missing letter A and :
 
Upvote 0
Thank you guys for the help. On the same topic, I'm try to use SUMIF to loop through my data set with the following code, but the output is not correct. Wrong numbers.

AAPL sum should be 1278.26, however its output is 2117.97




Code: 
With ActiveSheet    For i = 18 To .Range("W" & Rows.Count).End(xlUp).Row
        .Range("Y" & i) = WorksheetFunction.SumIf(.Range("A18:A" & Range("A" & Rows.Count).End(xlUp).Row), Range("W" & i), Range("P" & i))
    Next i
End With

[TABLE="width: 1199"]
<tbody>[TR]
[TD]AAP[/TD]
[TD]Advance Auto Parts[/TD]
[TD]05/06/2019[/TD]
[TD]05/08/2019[/TD]
[TD]2[/TD]
[TD]Option-P[/TD]
[TD]7[/TD]
[TD] $ 160.00[/TD]
[TD]06/21/2019[/TD]
[TD] $ 8.10[/TD]
[TD] $ 3.50[/TD]
[TD] $ 5,673.50[/TD]
[TD] $ 8.11[/TD]
[TD] $ 3.50[/TD]
[TD] $ 5,673.50[/TD]
[TD] $ 1.00[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[TD]Apple Inc.[/TD]
[TD]01/03/2019[/TD]
[TD]01/03/2019[/TD]
[TD]0[/TD]
[TD]Option-P[/TD]
[TD]5[/TD]
[TD] $ 145.00[/TD]
[TD]02/15/2019[/TD]
[TD] $ 9.15[/TD]
[TD] $ 6.45[/TD]
[TD] $ 4,581.62[/TD]
[TD] $ 8.70[/TD]
[TD] $ 6.45[/TD]
[TD] $ 4,343.55[/TD]
[TD] $ (238.07)[/TD]
[TD]-5.20%[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[TD]Apple Inc.[/TD]
[TD]01/03/2019[/TD]
[TD]01/03/2019[/TD]
[TD]0[/TD]
[TD]Option-C[/TD]
[TD]10[/TD]
[TD] $ 145.00[/TD]
[TD]01/18/2019[/TD]
[TD] $ 4.90[/TD]
[TD] $ 8.95[/TD]
[TD] $ 4,909.29[/TD]
[TD] $ 4.80[/TD]
[TD] $ 8.95[/TD]
[TD] $ 4,790.62[/TD]
[TD] $ (118.67)[/TD]
[TD]-2.42%[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[TD]Apple Inc.[/TD]
[TD]04/08/2019[/TD]
[TD]04/26/2019[/TD]
[TD]18[/TD]
[TD]Option-C[/TD]
[TD]5[/TD]
[TD] $ 200.00[/TD]
[TD]06/21/2019[/TD]
[TD] $ 8.22[/TD]
[TD] $ 3.50[/TD]
[TD] $ 4,113.50[/TD]
[TD] $ 9.51[/TD]
[TD] $ 2.50[/TD]
[TD] $ 4,752.50[/TD]
[TD] $ 639.00[/TD]
[TD]15.53%[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[TD]Apple Inc.[/TD]
[TD]05/01/2019[/TD]
[TD]05/01/2019[/TD]
[TD]0[/TD]
[TD]Option-C[/TD]
[TD]6[/TD]
[TD] $ 210.00[/TD]
[TD]06/21/2019[/TD]
[TD] $ 7.50[/TD]
[TD] $ 3.00[/TD]
[TD] $ 4,503.00[/TD]
[TD] $ 8.93[/TD]
[TD] $ 3.00[/TD]
[TD] $ 5,355.00[/TD]
[TD] $ 852.00[/TD]
[TD]18.92%[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[TD]Apple Inc.[/TD]
[TD]05/07/2019[/TD]
[TD]05/07/2019[/TD]
[TD]0[/TD]
[TD]Option-P[/TD]
[TD]3[/TD]
[TD] $ 205.00[/TD]
[TD]06/21/2019[/TD]
[TD] $ 8.20[/TD]
[TD] $ 1.50[/TD]
[TD] $ 2,461.50[/TD]
[TD] $ 8.69[/TD]
[TD] $ 1.50[/TD]
[TD] $ 2,605.50[/TD]
[TD] $ 144.00[/TD]
[TD]5.85%[/TD]
[/TR]
[TR]
[TD]ABT[/TD]
[TD]Abbott Laboratories[/TD]
[TD]02/22/2019[/TD]
[TD]02/26/2019[/TD]
[TD]4[/TD]
[TD]Option-C[/TD]
[TD]10[/TD]
[TD] $ 75.00[/TD]
[TD]05/17/2019[/TD]
[TD] $ 3.23[/TD]
[TD] $ 8.95[/TD]
[TD] $ 3,238.95[/TD]
[TD] $ 3.85[/TD]
[TD] $ 8.95[/TD]
[TD] $ 3,840.59[/TD]
[TD] $ 601.64[/TD]
[TD]18.58%[/TD]
[/TR]
[TR]
[TD]ABT[/TD]
[TD]Abbott Laboratories[/TD]
[TD]02/26/2019[/TD]
[TD]03/01/2019[/TD]
[TD]3[/TD]
[TD]Option-C[/TD]
[TD]15[/TD]
[TD] $ 77.50[/TD]
[TD]05/17/2019[/TD]
[TD] $ 2.43[/TD]
[TD] $ 11.45[/TD]
[TD] $ 3,656.45[/TD]
[TD] $ 3.10[/TD]
[TD] $ 11.45[/TD]
[TD] $ 4,638.55[/TD]
[TD] $ 982.10[/TD]
[TD]26.86%[/TD]
[/TR]
[TR]
[TD]ADBE[/TD]
[TD]Adobe Inc.[/TD]
[TD]04/29/2019[/TD]
[TD]05/10/2019[/TD]
[TD]11[/TD]
[TD]Option-C[/TD]
[TD]5[/TD]
[TD] $ 285.00[/TD]
[TD]06/21/2019[/TD]
[TD] $14.70[/TD]
[TD] $ 2.50[/TD]
[TD] $ 7,352.50[/TD]
[TD] $ 6.50[/TD]
[TD] $ 2.50[/TD]
[TD] $ 3,247.50[/TD]
[TD] $ (4,105.00)[/TD]
[TD]-55.83%[/TD]
[/TR]
[TR]
[TD]ADI[/TD]
[TD]Analog Devices[/TD]
[TD]04/16/2019[/TD]
[TD]05/03/2019[/TD]
[TD]17[/TD]
[TD]Option-C[/TD]
[TD]5[/TD]
[TD] $ 110.00[/TD]
[TD]06/21/2019[/TD]
[TD] $ 7.18[/TD]
[TD] $ 2.50[/TD]
[TD] $ 3,592.50[/TD]
[TD] $ 8.80[/TD]
[TD] $ 2.50[/TD]
[TD] $ 4,397.50[/TD]
[TD] $ 805.00[/TD]
[TD]22.41%[/TD]
[/TR]
[TR]
[TD]ADP[/TD]
[TD]Automatic Data Processing[/TD]
[TD]02/22/2019[/TD]
[TD]02/27/2019[/TD]
[TD]5[/TD]
[TD]Option-C[/TD]
[TD]5[/TD]
[TD] $ 150.00[/TD]
[TD]05/17/2019[/TD]
[TD] $ 6.50[/TD]
[TD] $ 6.45[/TD]
[TD] $ 3,256.45[/TD]
[TD] $ 6.98[/TD]
[TD] $ 6.45[/TD]
[TD] $ 3,483.30[/TD]
[TD] $ 226.85[/TD]
[TD]6.97%[/TD]
[/TR]
[TR]
[TD]ADP[/TD]
[TD]Automatic Data Processing[/TD]
[TD]03/25/2019[/TD]
[TD]03/25/2019[/TD]
[TD]0[/TD]
[TD]Option-C[/TD]
[TD]5[/TD]
[TD] $ 155.00[/TD]
[TD]06/21/2019[/TD]
[TD] $ 6.10[/TD]
[TD] $ 6.45[/TD]
[TD] $ 3,056.45[/TD]
[TD] $ 5.00[/TD]
[TD] $ 6.45[/TD]
[TD] $ 2,493.55[/TD]
[TD] $ (562.90)[/TD]
[TD]-18.42%[/TD]
[/TR]
[TR]
[TD]ADP[/TD]
[TD]Automatic Data Processing[/TD]
[TD]03/29/2019[/TD]
[TD]04/02/2019[/TD]
[TD]4[/TD]
[TD]Option-C[/TD]
[TD]7[/TD]
[TD] $ 160.00[/TD]
[TD]05/17/2019[/TD]
[TD] $ 4.50[/TD]
[TD] $ 7.45[/TD]
[TD] $ 3,157.45[/TD]
[TD] $ 5.50[/TD]
[TD] $ 7.45[/TD]
[TD] $ 3,842.55[/TD]
[TD] $ 685.10[/TD]
[TD]21.70%[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
If everything is on the same sheet, the With statement is not necessary, it can be like this:

Code: 
Dim i As Long, lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
For i = 18 To lr
  Range("Y" & i) = WorksheetFunction.SumIf(Range("A18:A" & lr), Range("W" & i), Range("P18:P" & lr))
Next i
 
Upvote 0
Wonderful!!! Thank you for the help and the insight on the with statement.
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Guinaba
  • Question Question
Tunning a VBA macro
Replies
1
Views
86
Skyybot
Skyybot
A
  • Question Question
Clock in and out 2 breaks and a lunch
Replies
0
Views
121
Aiden Jenner
A
A
  • Question Question
Looking to improve procedure
Replies
4
Views
107
AlexanderBB
A
A
  • Solved
Date format changing ...
Replies
4
Views
196
adambc
A
E
  • Solved
Delete Row if
Replies
6
Views
148
Eric Penfold
E

Forum statistics

Threads
1,223,893
Messages
6,175,244
Members
452,622
Latest member
Laura_PinksBTHFT

Share this page

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