VBA判斷單元格內容格式、顏色、合并單元格及返回數值（vba 合并單元格的值）

一、判斷數值的格式

'1 判斷是否為空單元格

Sub d1()

[b1] = ""

'If Range("a1") = "" Then

'If Len([a1]) = 0 Then

If VBA.IsEmpty([a1]) Then

[b1] = "空值"

End If

End Sub

'2 判斷是否為數字

Sub d2()

[b2] = ""

'If VBA.IsNumeric([a2]) And [a2] <> "" Then

'If Application.WorksheetFunction.IsNumber([a2]) Then

[b2] = "數字"

End If

End Sub

'3 判斷是否為文本

Sub d3()

[b3] = ""

'If Application.WorksheetFunction.IsText([A3]) Then

If VBA.TypeName([a3].Value) = "String" Then

[b3] = "文本"

End If

End Sub

'4 判斷是否為漢字

Sub d4()

[b4] = ""

If [a4] > "z" Then

[b4] = "漢字"

End If

End Sub

'5 判斷錯誤值

Sub d10()

[b5] = ""

'If VBA.IsError([a5]) Then

If Application.WorksheetFunction.IsError([a5]) Then

[b5] = "錯誤值"

End If

End Sub

Sub d11()

[b6] = ""

If VBA.IsDate([a6]) Then

[b6] = "日期"

End If

End Sub

二、設置單元格自定義格式

Sub d30()

Range("d1:d8").NumberFormatLocal = "0.00"

End Sub

三、按指定格式從單元格返回數值

'Format函數語法(和工作表數Text用法基本一致)

'Format(數值,自定義格式代碼)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

'Excel中的顏色可以用兩種方式獲取，一種是EXCEL內置顏色，另一種是利用QBCOLOR函數返回

Sub y1()

Dim x As Integer

Range("a1:b60").Clear

For x = 1 To 56

Range("a" & x) = x

Range("b" & x).Font.ColorIndex = 3

Next x

End Sub

Sub y2()

Dim x As Integer

For x = 0 To 15

Range("d" & x + 1) = x

Range("e" & x + 1).Interior.Color = QBColor(x)

Next x

End Sub

Sub y3()

Dim 紅 As Integer, 綠 As Integer, 藍 As Integer

紅 = 255

綠 = 123

藍 = 100

Range("g1").Interior.Color = RGB(紅, 綠, 藍)

End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

'單元格合并

Sub h1()

Range("g1:h3").Merge

End Sub

'合并區域的返回信息

Sub h2()

Range("e1") = Range("b3").MergeArea.Address '返回單元格所在 ? ? 的合并單元格區域

End Sub

'判斷是否含合并單元格

Sub h3()

'MsgBox Range("b2").MergeCells

' MsgBox Range("A1:D7").MergeCells

Range("e2") = IsNull(Range("a1:d7").MergeCells)

Range("e3") = IsNull(Range("a9:d72").MergeCells)

End Sub

'合并H列相同單元格

Sub h4()

Dim x As Integer

Dim rg As Range

Set rg = Range("h1")

Application.DisplayAlerts = False

For x = 1 To 13

If Range("h" & x + 1) = Range("h" & x) Then

Set rg = Union(rg, Range("h" & x + 1))

Else

rg.Merge

Set rg = Range("h" & x + 1)

End If

Next x

Application.DisplayAlerts = True

End Sub

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。