CountIf always counted 1 extra (VBA)

[zack8576]

I have this code to count the number of letter "R" in any given row, see test file below, these two rows contain 2x "R"

but my code is spitting out 3 every time... why is that?

code below:

  Dim ws1 As Worksheet
  Dim lrNew As Long, n As Long, n1 As Long, n2 As Long, sr As Long
  Set ws1 = ActiveSheet  

  lrNew = ws1.Range("K" & Rows.Count).End(xlUp).Row
  sr = 2
  If ws1.Cells(lrNew, "K").Value Like "*CS55*" Then
    n = WorksheetFunction.CountIf(ws1.Range("K" & sr & ":K" & lrNew), "*R*")
  End If
  MsgBox n

 

[rlv01]

Since we do not know what rows those are, or what might be in the rows above them or below them, there is no way to tell.

FWIW, when you post an image of your data instead of something that can be copied and pasted into a spreadsheet, it is difficult for others to experiment with it. Which means your chances of getting help drop significantly. Instead, use the free XL2BB tool (link below) to post your data in a way that makes it accessible to others.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[zack8576]

Since we do not know what rows those are, or what might be in the rows above them or below them, there is no way to tell.

FWIW, when you post an image of your data instead of something that can be copied and pasted into a spreadsheet, it is difficult for others to experiment with it. Which means your chances of getting help drop significantly. Instead, use the free XL2BB tool (link below) to post your data in a way that makes it accessible to others.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

okay thanks Rlv, this macro has to run on many excel files, these "CS55" "R" can appear on many rows, and any rows.
I actually have the test file uploaded on dropbox, see below, thanks !

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[rlv01]

okay thanks Rlv, this macro has to run on many excel files, these "CS55" "R" can appear on many rows, and any rows.
I actually have the test file uploaded on dropbox, see below, thanks !

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Unfortunately, for security reasons on this particular PC, I cannot DL random files from the internet. Your best bet is to post a small data subset that replicates the issue. Or perhaps someone else will step in.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[zack8576]

Unfortunately, for security reasons on this particular PC, I cannot DL random files from the internet. Your best bet is to post a small data subset that replicates the issue. Or perhaps someone else will step in.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

my Microsoft 365 blocks the xl2bb add-on every time I try to run it....

 

[Michael M]

The Drpbox file is different to the Screenshot in the first post ???

 

[zack8576]

The Drpbox file is different to the Screenshot in the first post ???

Michael, they are the same file, the screenshot only captured rows 10 and 11 of the file in dropbox

 

[rlv01]

2022100191 PAINT 2.csv
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Order#	Str#	Quantity	Item#	Job Req'd?	Piece#	Job#	Price	Source	Weight	Description	PART - Height (Int)	PART - Catalog ID	MH - Spec	MH - Type
2	CF22222	101	1	F14118J	Yes	P1	F22222DK01	0	Production	3973 lb	MH,4'dia,Base,5"w	18"	18"	FLORIDA	SANITARY
3	CF22222	101	1	F14272	No	P2	F22222DK02	0	Production	5203 lb	MH,4'dia,Riser,5"w	72"	72"	FLORIDA	SANITARY
4	CF22222	101	1	F14216	No	P3	F22222DK03	0	Production	1156 lb	MH,4'dia,Riser,5"w	16"	16"	FLORIDA	SANITARY
5	CF22222	101	1	F14332G	No	P4	F22222DK04	0	Production	1677 lb	MH,4'dia,Cone,5"w,SOG	30"	30"	FLORIDA	SANITARY
6	CF22222	101	1		No			0	Purchased	0 lb	Evergrip Sealant + 9" Wrapid-Seal	0"	170"/JOINT	FLORIDA	SANITARY
7	CF22222	101	1		No			0	Purchased	0 lb	Evergrip Sealant + 9" Wrapid-Seal	0"	170"/JOINT	FLORIDA	SANITARY
8	CF22222	101	1		No			0	Purchased	0 lb	Evergrip Sealant + 9" Wrapid-Seal	0"	170"/JOINT	FLORIDA	SANITARY
9	CF22222	101	1		No			0	Purchased	-59 lb	HOLE 12"	0"		FLORIDA	SANITARY
10	CF22222	101	1316.38 SqFt					0		0 lb	Paint,CS55,R/G/R	0"		FLORIDA	SANITARY
11	CF22222	101	1316.38 SqFt					0		0 lb	Paint,CS55,R/G/R	0"		FLORIDA	SANITARY
2022100191 PAINT 2

    Dim ws1 As Worksheet
    Dim SA As Variant
    Dim I As Long
    Dim lrNew As Long, n As Long, sr As Long
    
    Set ws1 = ActiveSheet

    lrNew = ws1.Range("K" & Rows.Count).End(xlUp).Row
    sr = 2
    SA = ws1.Range("K" & sr & ":K" & lrNew).Value
    For I = LBound(SA) To UBound(SA)
        If InStr(SA(I, 1), "CS55") Then
            n = n + Len(SA(I, 1)) - (Len(Replace(SA(I, 1), "R", "", 1, , vbBinaryCompare)))
        End If
    Next I
    MsgBox "R's found: " & n

 

[zack8576]

2022100191 PAINT 2.csv
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Order#	Str#	Quantity	Item#	Job Req'd?	Piece#	Job#	Price	Source	Weight	Description	PART - Height (Int)	PART - Catalog ID	MH - Spec	MH - Type
2	CF22222	101	1	F14118J	Yes	P1	F22222DK01	0	Production	3973 lb	MH,4'dia,Base,5"w	18"	18"	FLORIDA	SANITARY
3	CF22222	101	1	F14272	No	P2	F22222DK02	0	Production	5203 lb	MH,4'dia,Riser,5"w	72"	72"	FLORIDA	SANITARY
4	CF22222	101	1	F14216	No	P3	F22222DK03	0	Production	1156 lb	MH,4'dia,Riser,5"w	16"	16"	FLORIDA	SANITARY
5	CF22222	101	1	F14332G	No	P4	F22222DK04	0	Production	1677 lb	MH,4'dia,Cone,5"w,SOG	30"	30"	FLORIDA	SANITARY
6	CF22222	101	1		No			0	Purchased	0 lb	Evergrip Sealant + 9" Wrapid-Seal	0"	170"/JOINT	FLORIDA	SANITARY
7	CF22222	101	1		No			0	Purchased	0 lb	Evergrip Sealant + 9" Wrapid-Seal	0"	170"/JOINT	FLORIDA	SANITARY
8	CF22222	101	1		No			0	Purchased	0 lb	Evergrip Sealant + 9" Wrapid-Seal	0"	170"/JOINT	FLORIDA	SANITARY
9	CF22222	101	1		No			0	Purchased	-59 lb	HOLE 12"	0"		FLORIDA	SANITARY
10	CF22222	101	1316.38 SqFt					0		0 lb	Paint,CS55,R/G/R	0"		FLORIDA	SANITARY
11	CF22222	101	1316.38 SqFt					0		0 lb	Paint,CS55,R/G/R	0"		FLORIDA	SANITARY
2022100191 PAINT 2

    Dim ws1 As Worksheet
    Dim SA As Variant
    Dim I As Long
    Dim lrNew As Long, n As Long, sr As Long
   
    Set ws1 = ActiveSheet

    lrNew = ws1.Range("K" & Rows.Count).End(xlUp).Row
    sr = 2
    SA = ws1.Range("K" & sr & ":K" & lrNew).Value
    For I = LBound(SA) To UBound(SA)
        If InStr(SA(I, 1), "CS55") Then
            n = n + Len(SA(I, 1)) - (Len(Replace(SA(I, 1), "R", "", 1, , vbBinaryCompare)))
        End If
    Next I
    MsgBox "R's found: " & n

RLV, thank you !
care to explain what the vbBinaryCompare does in this code?

also, how do I modify the code so it only counts the number of "R" on any one row, not the total number of "R" between all relevant rows?

 

[rlv01]

care to explain what the vbBinaryCompare does in this code?

It makes the comparison case sensitive.

also, how do I modify the code so it only counts the number of "R" on any one row, not the total number of "R" between all relevant rows?

Your original VBA code example does not do that, it counts instances in all rows, so please illustrate what you mean by 'on any one row' and show what you do with the result. Use the data example I posted above to provides some example before and after values.