piao3701-CSDN博客

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 302

原创 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 4040

原创 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 4695

原创 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 139

原创 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 2224

原创 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 1867 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 192

原创 Excel公式技巧

计算某一列有数据的最后一行的行号MAX(NOT(ISBLAND(A:A))*ROW(A:A)) '该公式为数组公式，次案例计算A列最后一个数据所在行号

2021-01-11 15:06:13 64

原创 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 433

原创 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 1017

piao3701的博客

原创 Python日志模块logging

原创 VBA成绩录入