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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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
69
Skyybot
Skyybot
A
  • Question Question
Clock in and out 2 breaks and a lunch
Replies
0
Views
99
Aiden Jenner
A
A
  • Solved
Date format changing ...
Replies
4
Views
177
adambc
A
G
  • Question Question
How can I prevent duplicate record by validating two columns in my worksheet
Replies
1
Views
193
Engberg
E
E
  • Solved
This Code fills in below current month.
Replies
2
Views
181
Eric Penfold
E

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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