随笔分类 -  vba

vba学习笔记
连接Access数据库
摘要：Sub 连接数据库() '第一步：我们要用ADO 引用AD工具 '第二步：创建连接对象 '2-1: 给连接对象取名字，声明对象 'Dim conn As ADODB.Connection '声明对象变量'2-2: 创建对象变量，并赋值'Set conn = New ADODB.Connection 阅读全文
posted @ 2020-03-18 19:35 星翊 阅读(479) 评论(0) 推荐(0)
取字母
摘要：Sub text()Dim i As IntegerDim txt As StringDim texnkyy As Stringtexnyy = ""txt = "face"For i = 1 To Len(txt) ‘ Len =多少个字符If Not Mid(txt, i, 1) Like "[ 阅读全文
posted @ 2020-03-02 22:56 星翊 阅读(127) 评论(2) 推荐(0)
考试界面
摘要：Sub kaoshi(i As Integer) With Sheet2 '清空4个选项 .OptionButton1.Value = False .OptionButton2.Value = False .OptionButton3.Value = False .OptionButton4.Val 阅读全文
posted @ 2020-02-21 19:51 星翊 阅读(380) 评论(0) 推荐(0)
成绩表
摘要：Sub test()Dim i As IntegerDim j As Integer For j = 1 To Sheets.Count For i = 100 To 2 Step -1 If Sheets(j).Range("e" & i) = "男" Then Sheets(j).Range(" 阅读全文
posted @ 2020-02-14 22:51 星翊 阅读(277) 评论(0) 推荐(0)
多表汇总
摘要：Sub text()Dim i As IntegerFor i = 2 To Sheets.CountSheet1.Range("b" & i + 8) = Sheets(i).Range("e5")Sheet1.Range("c" & i + 8) = Sheets(i).Range("e6")S 阅读全文
posted @ 2020-02-12 22:09 星翊 阅读(131) 评论(0) 推荐(0)
生成工资条和恢复工资表
摘要：Sub gzt()Dim i As IntegerRows("1:1").Select ‘找到a1For i = 1 To 10Selection.Copy ActiveCell.Offset(2, 0).Rows("1:1").EntireRow.Select ’offset(行,列) Entir 阅读全文
posted @ 2020-02-11 22:30 星翊 阅读(651) 评论(0) 推荐(0)
九九乘法表
摘要：sub jjcfb () dim i,j as interger for i = 1 to 9 for j = i to 9 cells (j,i)= i & " * " j & " = " & i * j next next end sub 阅读全文
posted @ 2020-02-10 23:34 星翊 阅读(150) 评论(0) 推荐(0)
改日期
摘要：Sub zh() For i = 2 To Range("a65536").End(xlUp).Row Range("b" & i) = DateSerial(Left(Range("a" & i), 4), Mid(Range("a" & i), 5, 2), Right(Range("a" & 阅读全文
posted @ 2020-02-09 21:42 星翊 阅读(158) 评论(0) 推荐(0)
认识if语句
摘要：Sub 认识if语句() Dim i As Integer For i = 26 To 2 Step -1 If Range("e" & i) = "男" Then Range("f" & i) = "先生" Else Range("f" & i) = "女士" End If If Range("b 阅读全文
posted @ 2020-02-06 21:46 星翊 阅读(175) 评论(0) 推荐(0)
数据按列拆分（可选择）
摘要：Sub shi()Dim i, k, l As IntegerDim sht As Worksheetirow = Sheet1.Range("a65536").End(xlUp).Row l = InputBox("input name plase") For i = 2 To irowk = 0 阅读全文
posted @ 2020-02-05 23:34 星翊 阅读(266) 评论(2) 推荐(0)
按某列分表程序
摘要：Sub fenbiao()Dim sht As WorksheetDim i, k, irow As Integerirow = Sheet1.Range("a65536").End(xlUp).Row '定位表格最后一行 For i = 2 To irow k = 0 For Each sht I 阅读全文
posted @ 2020-02-05 23:01 星翊 阅读(273) 评论(0) 推荐(0)
vba十进制转二进制
摘要：sub stor ()dim stor,shi as long stor = "" n = range("b3") shi = range("a3") do while stor >0 stor = shi mod n & stor shi = shi / n loop range ("e3") = 阅读全文
posted @ 2020-02-04 12:44 星翊 阅读(1138) 评论(0) 推荐(0)
vba里设置读取背景和字体颜色
摘要：今天入住博客园，真HAPPY！！！！ 记录今天的学习情况 ：） sub readcolor() cells.interior.pattern=xlnone '清除背景颜色 range(“a1:k30").interior.color="&h00ffff" '设置背景色为黄色 range(“a1:k3 阅读全文
posted @ 2020-02-03 20:47 星翊 阅读(2705) 评论(1) 推荐(0)