ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
楼主: jsgj2023

[分享] 常用代码归集

  [复制链接]

TA的精华主题

TA的得分主题

 楼主| 发表于 2017-10-25 11:44 | 显示全部楼层
http://club.excelhome.net/thread-1375631-1-1.html
对选择区域的所有行的A-G列填充红色
Private Sub Worksheet_SelectionChange(ByVal T As Range)
    If T.Column > 7 Then Exit Sub
    If T.Row > 18 Then Exit Sub
    [a:g].Font.ColorIndex = 0
    Dim arr(1 To 2)
    R1 = Cells(Rows.Count, 1).End(xlUp).Row
    r2 = T.Address
    If InStr(r2, ":") Then
        s1 = Split(r2, ":")
        For x = 0 To UBound(s1)
            k = k + 1
            arr(k) = Split(s1(x), "$")(2)
        Next
        Range(Cells(arr(1) * 1, 1), Cells(arr(2) * 1, 7)).Font.ColorIndex = 3
    Else
        s = Split(r2, "$")(2) * 1
        Range(Cells(s, 1), Cells(s, 7)).Font.ColorIndex = 3
    End If
End Sub

TA的精华主题

TA的得分主题

 楼主| 发表于 2017-10-26 09:09 | 显示全部楼层
countif改写
http://club.excelhome.net/thread-1375735-1-1.html
Sub Adele()
    Sheet1.Activate
    Dim rng As Range, arr, brr, x&, k&, r&
    Range("a:a").Interior.ColorIndex = 0
    arr = Range("a1").CurrentRegion
    r = Cells(Rows.Count, 2).End(xlUp).Row
    brr = Range("b1:c" & r)
    For y = 1 To UBound(brr)
        If brr(y, 1) <> " " Then
            For x = 1 To UBound(arr)
                If InStr(arr(x, 1), brr(y, 1)) Then
                    k = k + 1
                     If rng Is Nothing Then Set rng = Cells(x, 1) Else Set rng = Union(rng, Cells(x, 1))
                    brr(y, 2) = k
                End If
            Next
            k = 0
        End If
    Next
    If Not rng Is Nothing Then rng.Rows.Interior.ColorIndex = 3
    Range("b1").Resize(UBound(brr), UBound(brr, 2)) = brr
End Sub

TA的精华主题

TA的得分主题

 楼主| 发表于 2017-10-26 10:23 | 显示全部楼层
http://club.excelhome.net/thread-1375840-1-1.html
请问怎样使用VBA进行数据引用
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$E$2" Then Exit Sub
sname = Target.Value
    Set d = CreateObject("scripting.dictionary")
     With Sheet2
        .ComboBox1.Clear
        arr = Sheet1.[a1].CurrentRegion
        For i = 2 To UBound(arr)
        If arr(i, 1) = sname Then
            If Not d.exists(arr(i, 2)) Then
                d(arr(i, 2)) = ""
                .ComboBox1.AddItem arr(i, 2)
            End If
        End If
        Next
    End With
End Sub

TA的精华主题

TA的得分主题

 楼主| 发表于 2017-10-26 10:44 | 显示全部楼层
http://club.excelhome.net/thread-1375840-1-1.html
请问怎样使用VBA进行数据引用

使用有效性
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$E$2" Then Exit Sub
sname = Target.Value
    Set d = CreateObject("scripting.dictionary")
     With Sheet2
        arr = Sheet1.[a1].CurrentRegion
        For i = 2 To UBound(arr)
        If arr(i, 1) = sname Then
            If Not d.exists(arr(i, 2)) Then
                d(arr(i, 2)) = ""
                With .Range("B5").Validation
                    .Delete
                    .Add 3, 1, 1, Join(d.keys, ",")
                End With
            End If
        End If
        Next
    End With
End Sub

TA的精华主题

TA的得分主题

 楼主| 发表于 2017-10-31 10:57 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助

汇总工作簿获取其他工作簿中的数据
http://club.excelhome.net/thread-1376520-1-1.html
Sub Adele()
Dim arr, brr, myPath$, d As Object, d1 As Object
Application.ScreenUpdating = False
Set d = CreateObject("scripting.dictionary")
Set d1 = CreateObject("scripting.dictionary")
myPath = ThisWorkbook.Path & "\样本-数据表"
f = Dir(myPath & "\*.xls*")
      Do While f <> ""
          If f <> ThisWorkbook.Name Then
              Set wb = Workbooks.Open(myPath & "\" & f)
              For Each sht In ActiveWorkbook.Worksheets
                  With sht
                      arr = .Range("a1").CurrentRegion
                      For x = 2 To UBound(arr)
                        If Len(arr(x, 2)) Then
                            d1(arr(x, 2)) = d1(arr(x, 2)) + arr(x, 10)
                            s = arr(x, 2) & "," & arr(x, 3)
                            d(s) = d(s) + arr(x, 10)
                        End If
                      Next x
                     End With
                 Next sht
                 wb.Close False
             End If
             f = Dir
         Loop
         On Error Resume Next
         For y = 4 To Cells(Rows.Count, 2).End(3).Row
            If d1.exists(Cells(y, 2).Value) Then Cells(y, 4) = d1(Cells(y, 2).Value) / 100000000
            s1 = Cells(y, 1).Value & "," & Cells(y, 2)
            If d.exists(s1) Then Cells(y, 4) = d(s1) / 100000000
         Next
Application.ScreenUpdating = True
End Sub

TA的精华主题

TA的得分主题

 楼主| 发表于 2017-11-2 11:55 | 显示全部楼层
数据拆分问题,希望大神们多多帮助
'http://club.excelhome.net/thread-1376659-1-1.html
Sub Adele()
    Dim arr, brr(), d As Object, crr(), Ma, Mat
    arr = Sheet1.Range("a1").CurrentRegion
    ReDim brr(1 To UBound(arr))
    Set d = CreateObject("Scripting.dictionary")
    Set Reg = CreateObject("Vbscript.RegExp")
    With Reg
        .Global = True
        .Pattern = "(?!适合)([\u4e00-\u9fa5]{2,})([0-9]?)|[A-Za-z]+"
    End With
    For x = 2 To UBound(arr)
        kk = kk + 1
        Set Mat = Reg.Execute(Mid(arr(x, 2), 6))
        For Each Ma In Mat
            m = m + 1: k = 0
            For y = 1 To Ma.submatches.Count
                k = k + 1
                If Ma.submatches(y - 1) = "" Then s = s & "," & Ma Else s = s & "," & Ma.submatches(y - 1)
            Next
        Next Ma
        brr(kk) = s: s = ""
    Next
    ReDim crr(1 To UBound(brr), 1 To 10)
    For a = 1 To UBound(brr)
        If Len(brr(a)) Then
            ss = Split(brr(a), ",")
            If UBound(ss) = 10 Then
                crr(a, 1) = ss(1): crr(a, 2) = ss(5): crr(a, 3) = ss(9): crr(a, 4) = ss(2) / 2
                crr(a, 5) = ss(1): crr(a, 6) = ss(7): crr(a, 7) = ss(9): crr(a, 8) = ss(2) / 2
                crr(a, 9) = ss(3): crr(a, 10) = ss(4)
            Else
                crr(a, 1) = ss(1): crr(a, 2) = ss(5): crr(a, 3) = ss(7): crr(a, 4) = ss(2)
                crr(a, 9) = ss(3): crr(a, 10) = ss(4)
            End If
        End If
    Next
Sheet3.Range("a23").Resize(UBound(crr), UBound(crr, 2)) = crr
End Sub

TA的精华主题

TA的得分主题

 楼主| 发表于 2017-11-2 14:56 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
如何把单元格数据批量生成文本文件
http://club.excelhome.net/thread-1377077-1-1.html
Sub Adele()
    Dim MyFile As Object, MyStr As String, r%, c%
    With Sheet1
            For r = 2 To .UsedRange.Rows.Count
            Set MyFile = CreateObject("Scripting.FileSystemObject") _
            .OpenTextFile(ThisWorkbook.Path & "\" & Cells(r, 2) & ".txt", 2, True)
                MyStr = ""
                For c = 1 To .UsedRange.Columns.Count
                    MyStr = MyStr & .Cells(r, c) & ","
                Next
                MyStr = Left(MyStr, (Len(MyStr) - 1))
                MyFile.WriteLine (MyStr)
            Next
        MyFile.Close
    End With
End Sub

TA的精华主题

TA的得分主题

发表于 2017-11-2 16:17 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
性痴则其志凝,故书痴者文必工,艺痴者技必良。
世之落拓而无成者,皆自谓不痴者也。

TA的精华主题

TA的得分主题

发表于 2017-11-2 17:36 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2017-11-2 21:30 | 显示全部楼层
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-6-16 15:50 , Processed in 0.032737 second(s), 6 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表