- 博客(13)
- 收藏
- 关注
RSS订阅原创 VBA成绩录入
Option ExplicitDim endColumnNum As Integer, firstColumnNum As IntegerPrivate Sub Worksheet_SelectionChange(ByVal Target As Range) Dim temp As Variant temp = Application.InputBox("请输入：", Type:=1) Debug.Print temp If CStr(temp) = CStr(False
2022-01-08 18:47:23
384
原创 VBA弹窗打开文件，获取文件名以及文件全路径
Sub t() On Error GoTo myErro '当没有选择打开任何文件时会报错，所以添加异常处理 Dim fileToOpen() As Variant, i As Long, filePaths() As String '设置筛选器，可同时选择多个文件 fileToOpen = Application _ .GetOpenFilename(filefilter:="Files (*.txt;*.xlsx), *.txt;*.xlsx",
2022-01-07 21:35:29
4477
原创 VBA打印机设置（列出所有打印机名字、选用合适的打印机并设计端口号）
Sub PrinterDemo() Dim WshNetwork As Object Dim oPrinter As Object Dim i As Long Set WshNetwork = CreateObject("WScript.Network") Set oPrinter = WshNetwork.EnumPrinterConnections For i = 0 To oPrinter.Count - 1 If i Mod 2 = 0
2022-01-07 14:20:43
5810
原创 FindFormatAndReplaceFormat
Sub FindFormatAndReplaceFormatDemo() Application.FindFormat.Interior.ColorIndex = 6 Application.ReplaceFormat.Interior.ColorIndex = 14 Cells.Replace what:="", Replacement:="", SearchFormat:=True, ReplaceFormat:=True '最后一句是关键，如果不写则起不到作用End
2022-01-07 13:35:17
175
原创 VBA炸弹游戏
Option ExplicitSub RePlay() Dim rng As Range, i As Long, j As Long Set rng = Worksheets(1).Range("B2:J11") rng.ClearContents rng.Interior.Color = xlNone For i = 2 To 11 For j = 2 To 10 If Rnd() > 0.9 Then Cells(i
2022-01-05 17:51:45
2339
原创 Excel VBA倒计时
Private myTime As DatePrivate Const endTime As Date = #11/25/2021 2:00:00 PM#Sub StartTimer() Call TimeOnEnd SubSub TimeOn() Dim deltaTime As Variant Dim days As Long, hours As Long, minutes As Long, seconds As Long myTime = Now()
2021-11-23 20:49:55
2144
1
原创 Excel循环换行
'在每一行的前五列单元格中填写数值，当列号为5时，下一个单元格跳到下一行A列，Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells(Target.Row, Target.Column) = InputBox("请输入：") If Target.Column <> 5 Then If Cells(Target.Row, Target.Column).Value Like "[qQ]"
2021-07-16 11:16:52
230
原创 Excel公式技巧
计算某一列有数据的最后一行的行号MAX(NOT(ISBLAND(A:A))*ROW(A:A)) '该公式为数组公式，次案例计算A列最后一个数据所在行号
2021-01-11 15:06:13
92
原创 VBA平均格式规则AddAboveAverage
Sub AboveAverage() Range("a1").Value = "Name" Range("b1").Value = "Value" Range("a2").Value = "Channel-1" Range("a2").AutoFill Destination:=Range("a2:a10"), Type:=xlFillDefault '自动填充 Range("b2:b10").FormulaArray = "=int(rand()*100)"
2020-11-24 08:54:54
496
原创 Excel VBA 重复数据删除（解决删选功能将第一行数据作为标题的问题）
Excel VBA 重复数据删除Sub DeleteDuplicates() Dim data, data_dict As Variant Dim LastRow, i, column_i As Integer LastRow = Range("A" & Rows.Count).End(xlUp).Row '找出数据列中最后数据所在行 data = Range("A1:A" & LastRow).Value '将数据赋值给变量 '利
2020-11-15 22:05:44
1157
空空如也
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人