help edit code vba!!!

Nguyen Anh Dung

Board Regular
Joined
Feb 28, 2020
Messages
133
Office Version
  1. 2016
Platform
  1. Windows
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!!!

Code:
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
1616743332928.png
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,170
Office Version
  1. 2016
Platform
  1. Windows
If you want to count number, why not use
Application.WorksheetFunction.Count ?
 

Nguyen Anh Dung

Board Regular
Joined
Feb 28, 2020
Messages
133
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,170
Office Version
  1. 2016
Platform
  1. Windows
If you are looking for specific prefix, you can use
=SUMPRODUCT(--(LEFT("H2:F" & j,1)="9"))
 

Nguyen Anh Dung

Board Regular
Joined
Feb 28, 2020
Messages
133
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

If you are looking for specific prefix, you can use
=SUMPRODUCT(--(LEFT("H2:F" & j,1)="9"))
i have edit but run error
1616749121579.png

Best regards,
Nguyen Anh Dung
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,170
Office Version
  1. 2016
Platform
  1. Windows
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)

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,816
Messages
5,638,496
Members
417,029
Latest member
lingx86

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
Top