count a value

[Forestq]

hi,

I have SAP file and I`m counting number of row in one order, example:

A	1
	1
	1
B	1
	1
C	1
	1
	1

<tbody>
</tbody>

I have code:

                For i = 2 To lastrow
                            If SAP_WS.Range("A" & i).Value <> "" Then
                            row_wynik = row_wynik + 1
                            ws.Cells(row_wynik, 1).Value = SAP_WS.Cells(i, 1)
                            ws.Cells(row_wynik, 2).Value = SAP_WS.Cells(i, 2)
                            
                        End If                
                Next i

How to count row number for A, B and C?

Result should be:
A=3
B=2
C=3


Please help

 

[JoeMo]

It appears from your post that you have an empty row above each entry in column A, except for the first entry in A2. Based on that layout, here's some code you can adapt to your needs that counts the rows for A,B,C, ...

Sub Counts()
'Assumes there is an empty row above each entry in column A except for the first entry in A2
Dim lastrow As Long, SAP_WS As Worksheet
Set SAP_WS = ActiveSheet
lastrow = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lastrow
    If SAP_WS.Range("A" & i).Value <> "" Then
        If SAP_WS.Range("A" & i).End(xlDown).Row < Rows.Count Then
            MsgBox "Count for " & SAP_WS.Range("A" & i).Value & " is " & _
                Range(Cells(i, "A"), Cells(i, "A").End(xlDown)).Rows.Count - 2
        Else
            MsgBox "Count for " & SAP_WS.Range("A" & i).Value & " is " & Range(Cells(i, "A"), Cells(Rows.Count, "B").End(xlUp)).Rows.Count
        End If
    End If
Next i
End Sub

 

[VBA Geek]

this formula should work too, if you have A, B, C in D1:D3, you would type starting in E1


=IF(D2="",COUNTA(INDEX($B$1:$B$10,MATCH(2,$B$1:$B$10)):INDEX($B$1:$B$10,MATCH($D1,$A$1:$A$10,0))),COUNTA(INDEX($B$1:$B$10,MATCH($D2,$A$1:$A$10,0)-1):INDEX($B$1:$B$10,MATCH($D1,$A$1:$A$10,0))))

 

[Forestq]

Hi JoeMo

I have value as below:

A	1
	1
	1
B	1
	1
C	1
D	1
	1
E	1
	1

<tbody>
</tbody>

after I run your code (I change a little) I still got wrong number:
sum A= 3
sum B= 2
sum C= 4
sum D= 3
sum E= 2

my full code

Sub Counts1()
'Assumes there is an empty row above each entry in column A except for the first entry in A2

Application.DisplayAlerts = False
Application.ScreenUpdating = False


Dim ws As Worksheet
Dim wb As Workbook


Set wb = ActiveWorkbook
Set ws = wb.Worksheets("WYNIK")

ws.Activate
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select

Dim FileToOpen As String

    FileToOpen = OpenFile("Open SAP File", "*.xls", ThisWorkbook.Path & "")
         
        If FileToOpen <> "" Then
         
            Dim SAP_WB As Workbook
            Dim SAP_WS As Worksheet
            
            Set SAP_WB = Workbooks.Open(FileToOpen)
            Set SAP_WS = SAP_WB.Worksheets(1)
                                
            SAP_WS.Activate
            Columns("A:L").Select
            Selection.Delete Shift:=xlToLeft
            Columns("B:H").Select
            Selection.Delete Shift:=xlToLeft
            Columns("B:F").Select
            Selection.Delete Shift:=xlToLeft
            Columns("C:C").Select
            Selection.Delete Shift:=xlToLeft
            Range("B2").Select
            Selection.Delete Shift:=xlUp
            Range("A1").Select
            Selection.EntireRow.Delete
            Range("A1:A3").Select
            Selection.EntireRow.Delete
            Range("A1").Select
                    
Dim lastrow As Long
Set SAP_WS = ActiveSheet
lastrow = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To lastrow
    If SAP_WS.Range("A" & i).Value <> "" Then
        If SAP_WS.Range("A" & i).End(xlDown).Row < Rows.Count Then
        '    MsgBox "Count for " & SAP_WS.Range("A" & i).Value & " is " & _
         '       Range(Cells(i, "A"), Cells(i, "A").End(xlDown)).Rows.Count '- 1
        
            MsgBox "Count for " & SAP_WS.Range("A" & i).Value & " is " & _
                Range(Cells(i, "B"), Cells(i, "B").End(xlDown)).Rows.Count '- 1
           
        Else
            MsgBox "Count for " & SAP_WS.Range("A" & i).Value & " is " & Range(Cells(i, "A"), Cells(Rows.Count, "B").End(xlUp)).Rows.Count
        End If
    End If
Next i

End If

End Sub

 

[Forestq]

my working code:

                For i = 1 To lastrow
                        
                        If SAP_WS.Range("A" & i).Value <> "" Then
                            If SAP_WS.Range("A" & i).Value <> "" And SAP_WS.Range("A" & i).Offset(1, 1).Value <> "" Then
                                    
                                    count_qty = Range(Cells(i, "A"), Cells(i, "B").End(xlDown)).Rows.Count
                                    
                                    row_wynik = row_wynik + 1
                                    ws.Cells(row_wynik, 1).Value = SAP_WS.Cells(i, 1)
                                    ws.Cells(row_wynik, 2).Value = SAP_WS.Cells(i, 2)
                                    ws.Cells(row_wynik, 3).Value = count_qty
                            Else
                                    row_wynik = row_wynik + 1
                                    ws.Cells(row_wynik, 1).Value = SAP_WS.Cells(i, 1)
                                    ws.Cells(row_wynik, 2).Value = SAP_WS.Cells(i, 2)
                                    ws.Cells(row_wynik, 3).Value = "1"
                                    
                            End If
                        End If                
                Next i

 

[VBA Geek]

hi forestq, just out of curiosity, did my formula work on your dataset?

 

[Forestq]

after I put it on column D it didn't work, I take tip from JoeMo and changed my code.

 

[VBA Geek]

ok thanks for feedback

i was getting good results with it out of your sample

 

[Forestq]

thank you anyway VBA Geek !