vba对单元格的操作

excel 中颜色获取

Sub t1()
Dim hong As Integer, lv As Integer, lan As Integer
hong = 255
lv = 255
lan = 255
Range("g1").Interior.Color = RGB(hong, lv, lan)

End Sub

 

一、判断单元格数值格式 

1 判断是否为空单元格

Sub t2()
[b1] = ""

'If Range("a1") = "" Then
'If Len([a1]) = 0 Then

If VBA.IsEmpty([a1]) Then
    [b1] = "空值"
  End If

End Sub

 

2 判断是否为数字

Sub t3()
[b2] = ""
'If VBA.IsNumeric([a2]) And [a2] <> "" Then
If Application.WorksheetFunction.IsNumber([a2]) Then
    [b2] = "数字"
    End If

End Sub

' 判断是否为汉字

Sub t4()
[b4] = ""
If [a4] > "z" Then
    [b4] = "汉字"
End If
End Sub

判断错误值

Sub t5()
[b5] = ""
'If VBA.IsError([a5]) Then
If Application.WorksheetFunction.IsErr([a5]) Then
    [b5] = "错误值"
End If
End Sub

判断日期

Sub t6()
[b6] = ""
If VBA.Date([a6]) Then
    [b6] = "日期"
End If
End Sub

二、 设置单元格自定义格式

Sub t7()
Range("a8:a11").NumberFormatLocal = "0.00"
End Sub

单元格的合并

Sub t8()
    Range("a8:c8").Merge
End Sub

合并区域的返回值

Sub t9()
Range("a9") = Range("a8").MergeArea.Address ' 返回单元格所在的合并单元格

End Sub

 

判断是否含合并单元格

Sub t10()
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

综合案例
合并K列相同单元格

Sub t11()

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
    Application.DisplayAlerts = True
    Set rg = Range("h" & x + 1)
    End If
Next x

End Sub

 

单元格的输入

Sub t1()
Range("a1") = "a" & "b"
Range("b1") = "a" & char(10) & "b" '换行输入
End Sub

单元格复制与剪切

Sub t2()
Range("a1:a10").Copy Range("c1") ' a1：a10 的内容复制到c1

End Sub

Sub t3()
Range("a1:a10").Copy
ActiveSheet.Paste Range("d1") ' 粘贴至d1

End Sub

Sub t4()
Range("a1:a10").Copy
rnge("a1").PasteSpecial (xlPasteValues) '只粘贴为数值
End Sub


Sub t5()
Range("a1:a10").cat
ActiveSheet.Paste Range("f1") '粘贴到f1

End Sub


Sub t6()
Range("a1:a10").Copy
Range("c1:c10").PasteSpecial operation:=xlAdd   '选择粘贴-加
End Sub


Sub t7()
Range("G1:G10") = Range("a1:a10").Value
End Sub

 

填充公式

Sub t8()
Range("b1") = "=a1*10"
Range("b1:b10").FillDown '向下填充公式
End Sub

单元格的插入

Sub q1()
Range(4).Insert  '在第4行插入一行

End Sub

插入行并赋值公式

Sub q2()
Range(4).Insert
Range("3:4").FillDown ' 复制第三行内容
Range("4:4").SpecialCells(xlCellTypeConstants) = "" '将内容清空，留下公式
End Sub

案例  插入行

Sub c3()
Dim x As Integer
For x = 2 To 20
    If Cells("3:4") <> Cells(x + 1, 3) Then
        Range(x + 1).Insert
        x = x + 1
    End If
    Next x
End Sub

 

 

案例 统计不同类型的商品总数

Sub c4()
Dim x As Integer, m1 As Integer, m2 As Integer
Dim k As Integer
m1 = 2
For x = 2 To 1000
    If Cells(x, 1) = "" Then Exit Sub
    If Cells(x, 3) <> Cells(x + 1, 3) Then
        m2 = x
        Range(x + 1).Insert
        Cells(x + 1, "e") = Cells(x, "e") & "小计"
        Cells(x + 1, "h") = "=sum(h" & m1 & ":h" & m2 & ")"
        Cells(x + 1, "h").Resize(1, 4).FillRight
        Cells(x + 1, "i") = ""
        x = x + 1
        m1 = m2 + 2
        End If
    Next x

End Sub

 

 

案例  删除空格

Sub dd()
Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
' 删除中间空行
End Sub