help edit code vba!!!

[Nguyen Anh Dung]

i have code as below. when i use Xy = Application.WorksheetFunction.CountIf(.Range("H2:H" & j), "9*") count character begin is 9 then only count cell is string. if cells format general or number then not count.
Help me for this case string and number!!!

Sub CountData()

Dim fso As Object, xlFile As Object

Dim sFolder$

Dim r&, j&
'On Error Resume Next
r = 1
Application.DisplayAlerts = False
Application.AskToUpdateLinks = False

Set fso = CreateObject("Scripting.FileSystemObject")

With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = True
.InitialFileName = ThisWorkbook.path

If .Show Then sFolder = fso.GetParentFolderName(.SelectedItems(1)) Else Exit Sub 'chon file

End With
For Each xlFile In fso.GetFolder(sFolder).Files
With Workbooks.Open(xlFile.path)

With .Sheets(1)

j = .Cells(.Rows.count, 1).End(xlUp).Row
CA = Application.WorksheetFunction.CountA(.Range("E2:E" & j)) 'tong hinh
SA = Application.WorksheetFunction.CountA(.Range("C2:C" & j)) 'dem o co du lieu cot sub

TA = Application.WorksheetFunction.CountIf(.Range("F2:F" & j), "?*") 'name
XX = Application.WorksheetFunction.CountIf(.Range("F2:F" & j), "Nhà ?") 'nha o
'Xy = Application.WorksheetFunction.CountIf(.Range("H2:H" & j), "9*") + Application.WorksheetFunction.CountIf(.Range("H2:H" & j), "12*") + Application.WorksheetFunction.CountIf(.Range("H2:H" & j), "16*")
Xy = Application.WorksheetFunction.CountIf(.Range("H2:H" & j), "9*")
ZA = Application.WorksheetFunction.CountIf(.Range("C2:C" & j), "9385001")
XX1 = Application.WorksheetFunction.CountIf(.Range("C2:C" & j), " ")
XX2 = Application.WorksheetFunction.CountIf(.Range("F2:F" & j), " ")
Xz = Application.WorksheetFunction.CountIf(.Range("C2:C" & j), "9968017")
TA1 = Application.WorksheetFunction.CountIf(.Range("C2:C" & j), "7328002") + Application.WorksheetFunction.CountIf(.Range("C2:C" & j), "7397001")
xz5 = Application.WorksheetFunction.CountIf(.Range("C2:C" & j), "9968022") 'tru dien
xz2 = Application.WorksheetFunction.CountIf(.Range("C2:C" & j), "9932013") 'tru cuu hoa
xz3 = Application.WorksheetFunction.CountIf(.Range("C2:C" & j), "9942002") 'tram xe buyt
xz4 = Application.WorksheetFunction.CountIf(.Range("C2:C" & j), "9961023")
xz6 = Application.WorksheetFunction.CountIf(.Range("C2:C" & j), "9959032") + Application.WorksheetFunction.CountIf(.Range("C2:C" & j), "9959033")
xz7 = Application.WorksheetFunction.CountIf(.Range("A2:A" & j), "") + Application.WorksheetFunction.CountIf(.Range("A2:A" & j), "*")
xz8 = Application.WorksheetFunction.CountIf(.Range("F2:F" & j), "*'*") + Application.WorksheetFunction.CountIf(.Range("J2:J" & j), "*'*") + Application.WorksheetFunction.CountIf(.Range("F2:F" & j), "*!*") + Application.WorksheetFunction.CountIf(.Range("J2:J" & j), "*!*") + Application.WorksheetFunction.CountIf(.Range("J2:J" & j), "*\") + Application.WorksheetFunction.CountIf(.Range("J2:J" & j), "*\*") + Application.WorksheetFunction.CountIf(.Range("F2:F" & j), "*\*") + Application.WorksheetFunction.CountIf(.Range("F2:F" & j), "*\")
'xz7 = Application.WorksheetFunction.CountIf(.Range("A2:A" & j), "*")
'xz7 = Application.WorksheetFunction.CountIfs(.Range("A2:A" & j), "", .Range("A2:A" & j), "<>" & "{0,1,2,3,4,5,6,7,8,9}")
'CountIf(.Range("A2:A" & j), "*"): text, special characters, blank
'CountIf(.Range("A2:A" & j), "?*"): text, special characters
'COUNTIF(A1:A11,”?*”) + SUMPRODUCT(–(ISLOGICAL(A1:A11))text, special characters, logical value
End With
.Close False
End With
r = r + 1
Cells(r, 1).Value = xlFile.Name
Cells(r, 2).Value = CA
Cells(r, 3).Value = SA
Cells(r, 4).Value = TA
Cells(r, 5).Value = ZA
Cells(r, 9).Value = XX
Cells(r, 10).Value = Xy 'sodthoai
Cells(r, 11).Value = XX1 'khoang cach cot code
Cells(r, 12).Value = XX2 ''khoang cach cot name
Cells(r, 13).Value = Xz 'sai ma duong
Cells(r, 14).Value = TA1 ' atm_ngan hang
Cells(r, 15).Value = xz5 'tru dien
Cells(r, 16).Value = xz2 'tru cuu hoa
Cells(r, 17).Value = xz3 'tram xe buyt
Cells(r, 18).Value = xz4 'den tin hieu
Cells(r, 19).Value = xz6
Cells(r, 20).Value = xz7
Cells(r, 21).Value = xz8
Next
MsgBox "Ho" & ChrW(224) & "n Th" & ChrW(224) & "nh !!!"
Application.AskToUpdateLinks = True
Application.DisplayAlerts = True

End Sub

 

[Zot]

If you want to count number, why not use
Application.WorksheetFunction.Count ?

 

[Nguyen Anh Dung]

If you want to count number, why not use
Application.WorksheetFunction.Count ?

Thanks you reply. yes, i want count both. number and string!!!
Best regards,
Nguyen Anh Dung

 

[Zot]

If you are looking for specific prefix, you can use
=SUMPRODUCT(--(LEFT("H2:F" & j,1)="9"))

 

[Nguyen Anh Dung]

If you are looking for specific prefix, you can use
=SUMPRODUCT(--(LEFT("H2:F" & j,1)="9"))

i have edit but run error

Best regards,
Nguyen Anh Dung

 

[Zot]

i have edit but run error
View attachment 35371
Best regards,
Nguyen Anh Dung

I never thought it could not work in VBA since it works in Excel.

I'm wondering if this can be achieve without looping

 

[Zot]

i have edit but run error
View attachment 35371
Best regards,
Nguyen Anh Dun

Right now I can think is just call this UDF

Usage
Xy = Count("9",.Range("H2","H" & j)

Function Count(Fnd As String, rngLook As Range)

Dim cell As Range
Dim L As Long

L = Len(Fnd)

For Each cell In rngLook
    If Left(cell, L) = Fnd Then Count = Count + 1
Next

End Function

 

[Nguyen Anh Dung]

I never thought it could not work in VBA since it works in Excel.

I'm wondering if this can be achieve without looping

yes, thanks you!!!