實(shí)例39-多個(gè)工作表指定區(qū)域單元格統(tǒng)計(jì)

Private Sub CommandButton獲取_Click()

'獲取工作簿中包含的工作表

With ThisWorkbook.Worksheets("統(tǒng)計(jì)結(jié)果") '清除原列表數(shù)據(jù)

.UsedRange.ClearFormats

.UsedRange.ClearContents

End With

Dim wbname As String

Dim findrange As String

With ThisWorkbook.Worksheets("操作界面")

If .Cells(2, "C").Value <> "" Then

wbname = .Cells(2, "C").Value

Else

MsgBox "請(qǐng)輸入工作簿名稱（包含擴(kuò)展名）"

Exit Sub

End If

If .Cells(5, "C").Value <> "" Then

findrange = .Cells(5, "C").Value

Else

MsgBox "請(qǐng)輸入查詢的區(qū)域地址"

Exit Sub

End If

End With

Dim i As Integer

ThisWorkbook.Worksheets("統(tǒng)計(jì)結(jié)果").Cells(1, 1).Value = "名稱"

ThisWorkbook.Worksheets("統(tǒng)計(jì)結(jié)果").Cells(1, 2).Value = "區(qū)域"

ThisWorkbook.Worksheets("統(tǒng)計(jì)結(jié)果").Cells(1, 3).Value = "日期單元格數(shù)量"

ThisWorkbook.Worksheets("統(tǒng)計(jì)結(jié)果").Cells(1, 4).Value = "數(shù)值單元格數(shù)量"

ThisWorkbook.Worksheets("統(tǒng)計(jì)結(jié)果").Cells(1, 5).Value = "空單元格數(shù)量"

ThisWorkbook.Worksheets("統(tǒng)計(jì)結(jié)果").Cells(1, 6).Value = "文本單元格數(shù)量"

ThisWorkbook.Worksheets("統(tǒng)計(jì)結(jié)果").Cells(1, 7).Value = "全部單元格數(shù)量"

Dim j, cellitem

Dim numcount As Long

Dim nullcount As Long

Dim strcount As Long

Dim datecount As Long

For i = 1 To Workbooks(wbname).Worksheets.Count

numcount = 0

nullcount = 0

datecount = 0

With Workbooks(wbname).Worksheets(i)

ThisWorkbook.Worksheets("統(tǒng)計(jì)結(jié)果").Cells(i + 1, 1).Value = .Name

For Each cellitem In .Range(findrange)

If cellitem = "" Then

nullcount = nullcount + 1

Else

If IsNumeric(cellitem) = True Then

numcount = numcount + 1

End If

If IsDate(cellitem) = True Then

datecount = datecount + 1

End If

End If

Next cellitem

ThisWorkbook.Worksheets("統(tǒng)計(jì)結(jié)果").Cells(i + 1, 2).Value = findrange

ThisWorkbook.Worksheets("統(tǒng)計(jì)結(jié)果").Cells(i + 1, 3).Value = datecount

ThisWorkbook.Worksheets("統(tǒng)計(jì)結(jié)果").Cells(i + 1, 4).Value = numcount

ThisWorkbook.Worksheets("統(tǒng)計(jì)結(jié)果").Cells(i + 1, 5).Value = nullcount

ThisWorkbook.Worksheets("統(tǒng)計(jì)結(jié)果").Cells(i + 1, 6).Value = .Range(findrange).Cells.Count - datecount - numcount - nullcount

ThisWorkbook.Worksheets("統(tǒng)計(jì)結(jié)果").Cells(i + 1, 7).Value = .Range(findrange).Cells.Count

End With

Next i

ThisWorkbook.Worksheets("統(tǒng)計(jì)結(jié)果").Activate

End Sub

實(shí)例40-將日期轉(zhuǎn)換為文本

Private Sub CommandButton處理_Click()

With Worksheets("處理結(jié)果")

.Columns(2).ClearContents

.Columns(3).ClearContents

.Columns(4).ClearContents

.Columns(5).ClearContents

.Columns(6).ClearContents

Dim datey As Long

Dim datem As Long

Dim dated As Long

For i = 1 To .Range("A1000000").End(xlUp).Row

If .Cells(i, 1) <> "" And IsDate(.Cells(i, 1)) = True Then

datey = Year(.Cells(i, 1))

datem = Month(.Cells(i, 1))

dated = Day(.Cells(i, 1))

.Cells(i, 2) = "'" & datey & "/" & datem & "/" & dated

.Cells(i, 3) = datey

.Cells(i, 4) = datem

.Cells(i, 5) = dated

.Cells(i, 6) = "'" & datey & "年" & datem & "月" & dated & "日"

End If

Next i

.Activate

End With

End Sub