Excel_VBA
取啥都被占用
是个业余自学编程的童鞋
展开
-
Excel_VBA:根据条件设定背景色
我之前没有学过学过VBA,原创 2014-08-05 08:45:11 · 3360 阅读 · 0 评论 -
山寨HeatMap注意事项
今天翻邮件,翻到一封HeatMap。感受下。要从空的Scatter开始做,加入X轴和Y轴数据,SeriesName其实可以不用。接下来就是调背景色了。每个小箭头,都可以选颜色的。然后最后附上一段宏。可用来把小标签名字都换成行名称替换。选的DataRange其实是B:C 结果标签居然用的是ColumnA哦。全因为一段VBA。当你要用这段VBA时候,鼠标要选原创 2015-10-10 01:45:23 · 428 阅读 · 0 评论 -
【来日复制粘贴】捕捉字符串里面的日期的公式
Function FindDate(Rng As Range) 'Dim RowN As Long Dim Sentence As String Dim k As Integer 'Dim Send As Long Sentence = Rng.Value 'Record the cont原创 2015-09-11 18:01:48 · 513 阅读 · 0 评论 -
httpxml.send->拒绝访问错误80070005的解决方法
今天练习用vba抓网页信息遇到一个问题,当创建对象Microsoft.XMLHTTP以后,使用http.xml发送访问请求的时候,被拒绝了。这种一般和安全性有关,本地程序阻止了你的访问请求。解决的一个办法办法是把Microsoft.XMLHTTP换成MSXML2.ServerXMLHTTP.6.0另外一个方式就是把那个网页加到信任站点里面就可以了。原创 2015-08-16 00:51:14 · 7338 阅读 · 3 评论 -
【来日复制粘贴】输出匹配到的字符串
目前用正则还是很少,用到的话 一般也是replace那个函数。一直头疼我要输出找到的东西咋整?原来是用数组搞定。代码如下,Sub RegTest() '定义正则表达式对象 Dim oRegExp As Object '定义匹配字符串集合对象 Dim oMatches As Object '创建正则表达式 '定义要执行正则查找的文本变量原创 2015-10-02 01:02:06 · 533 阅读 · 0 评论 -
【来日复制粘贴】状态栏随机显示
最近都在折腾别的,没有好好编程,不开心。今天凑巧看到一个我老是会磕绊的地方,特此记录。另外,整个这个程序当滚动条思路还是不错的。大意就是收款时间那里为空的就是还欠账呢,然后可以让他的大名去状态栏滚动随机播放。下面上代码:Dim arr(), i Sub auto_open() Dim sht As Worksheet, rng As Range, FindRng As R原创 2015-08-28 09:35:07 · 494 阅读 · 0 评论 -
【来日复制粘贴】提取工作表和工作簿名称
在过Excel 函数与公式的时候,看到一个可能以后在写vba时候能够使用的长公式。特此记录。效果:具体公式:=CELL("filename",$A$1)=MID(B2,FIND("]",B2,1)+1,100)=REPLACE(REPLACE(B2,1,FIND("[",B2,1),""),FIND("]",REPLACE(B2,1,FIND("[",B2,1)原创 2015-07-20 10:58:23 · 769 阅读 · 0 评论 -
Rand以后每个国家Sample50个记录
需求:有如下原始数据,数据透视表可以看到有些国家的sample多,有些少。要满足把少于等于50个记录的国家先都抽出来复制到样本表中,接着那些大于50个记录的国家,需要随机打乱顺序后,各国也抽取五十个记录放到样本表中。下面是第一次试验成功的代码:Option ExplicitSub Test()'在开始以前请在表格最右边加一列,列名为SerialDim i As Inte原创 2015-03-28 10:37:11 · 693 阅读 · 0 评论 -
CorpAct抽样模板
最近有个项目的抽样也是够恶心,原始数据表包含一张全的公司list(无重复公司)。交给Ops Team去根据它抽数。接着对方返回一个excel 包含母list 和 多个data group sheet,每个data group里面就是抽到的,在指定时间段内的数据。在被抽到的data group 中,总共抽取30个公司的数据。要求每个data group 都要random到两个记录。剩下的部分原创 2015-07-22 21:08:42 · 478 阅读 · 0 评论 -
VBA传参之想哭的心
今天一个小程序,写起来一会儿,结果debug了一个小时。最后发现是传参的问题。总的来说,VBA传参有两个办法。方法一,清吧要传入的变量都搞成Public。方法二,可以不是Public,那么那个参数的Prototype 必须是那几个传参变量的名字。比如我要传 iii即RegTest (iii),那么需要 RegTest (iii as integer)。想哭。写了也不少了,怎么还是在犯这原创 2015-12-25 18:27:06 · 720 阅读 · 0 评论 -
我是凑数王
今天客户让我算Category Credit Qual 的Breakdown。这几万条数据的,我怎么给他凑出来那个数。于是想到了之前一个凑数模板,在此基础上修改一下,就能用来抓住里面的各元素,再倒回去分析。特此贴一个裸版。Public arr1Sub AvgCalculator() 'To assign column K to one dimensional array原创 2016-02-02 14:51:51 · 821 阅读 · 0 评论 -
vba 爬虫常用对象和方法
最近又要小爬一下动态网页,于是复习了一下常用对象。Sub WebCrawler(ByRef Item, ByRef DraftPage)Dim sKey As StringDim k As Integer sKey = "Time In Source Status" k = 0Dim GUrl As String GUrl = "https://msjira.ooo.com/b原创 2017-01-20 10:56:20 · 16998 阅读 · 3 评论 -
吐血记vba out of memory问题
今天花了两个多小时查一个看着有点吓人,但是并不影响结果的错误。毕竟那个错误的名字叫out of memory,像我神经那么大条的,都不敢忽略。然而过程是曲折的,答案是无趣的。。。但应该是个普遍容易忽视的问题,特此记一笔。症状:程序也不大,逐步debug,每次到end sub或exit sub那里,就报个out of memory...大概原因:犯懒,迁移以前写的原创 2017-12-05 12:24:26 · 2507 阅读 · 0 评论 -
VBA发Out-of-office in outlook
单位的Jira 和Outlook联系的不够紧密,总之OOO的状态无法让那些Jira上发任务Ticket的人知道,这么着还是挺容易误事的。遂做了一个VBA 工具,小规模解决下这个问题。Private WithEvents Items As Outlook.Items'Create for Jira OutOfOffice Notice email in outlook'Please rev原创 2017-09-26 15:43:30 · 600 阅读 · 0 评论 -
Index Match beat Vlookup的场景
有一批不同的投资组合,每个投资组合各自有时间序列,要求对每个投资组合每期,求一个标准差。=IF(A4=A2,C3+STDEV(INDEX(Sheet1!C:C,MATCH(Sheet1!A4,Sheet1!A:A,0)):C3)*2.5,"")用Evaluate看看就知道了。原创 2017-05-16 17:40:56 · 359 阅读 · 0 评论 -
VBA爬虫小试
因为进不去数据库今天终于需要实战VBA网页爬虫了。370条记录,用时三分钟。想说其实挺慢的。以后慢慢改进吧。抓下来之后采用Text to Columns 用着刚刚好。Sub Crawler() Dim xmlhttp As Object Dim strURL As String Dim i As Integer Dim rowNum As Integer原创 2015-11-06 16:09:00 · 10691 阅读 · 4 评论 -
整行排序时Vlookup 优于 Index Match
在做Ticket时候发现,虽然Index Match组合很显灵活,但是对于整行排序的时候,它就不如vlookup了。vlookup的第一个参数是会跟随排序动的。但是用Match的时候,那个里面的lookup value,在不设置的前提下是原来的地址。可以推断match这个函数的底层其实是个 refer to address的函数,而不是个ref to value。原创 2016-07-06 12:35:39 · 1021 阅读 · 2 评论 -
确实还是字典法方便
好久没有来充数了,废话也是要写写的。 终于稍微尝到了一点OO的甜头。因为一直用不好OO,一直逃避,能不用就不用。正则什么的,都是没办法了才用。这次为了页面和代码的简单易懂,在不重复List的问题上,还是选择了字典法。'********************************APT vs TPTDim DicTest As Object'Here apply non dup by dicti原创 2016-04-11 12:05:10 · 433 阅读 · 0 评论 -
VBA的CountIf错误:要求对象
今天为何作死晚睡?因为遇到了这个424错误。解决方法就是请先选中那个sheet, 对sheet.select然后再用countif,并且这个时候所有的sheet都变为Activesheet。然后就不会有那个错误了!原创 2016-02-17 00:23:13 · 2408 阅读 · 1 评论 -
【理解】如何在字符串中攫取单个字符
今天了解函数公式,看到了一个思路很不错的字符串攫取方式。首先上公式,E列为价值。=Right(REPT(" ",8)&"¥"&$E5*100,8)=Right(" ¥276000",8)=" ¥276000"接下来要从这个结果字符串里面把单一字符对应写到角分个十百千万。这里看到一个很巧妙的想法。=IF($E5,MID(RIGHT(REPT(" ",8)&"¥"&$原创 2015-07-26 10:48:24 · 539 阅读 · 0 评论 -
【AutoMarkTool】Private Sub如何放入Module使用
需求:在日常audit时候,判断完数据问题以后,还要根据不同数据问题选择不同defect type。现希望只要判断数据问题,在选择defect type环节降低错误率和劳动成本。因为毕竟判断数据问题才是关键,这块是主要价值,手动选择defect type是个繁琐的小动作虽然对于结果是必要的,但是价值的根本不在它,它只是价值体现。面对大量数据的时候,选择defect type会有一定小概率的出错原创 2015-06-11 11:23:47 · 895 阅读 · 0 评论 -
【来日复制粘贴】利用函数公式解决Tricky的不重复记录高亮
今天在学习Excel的时候,看到了一则很实用的技巧说明,下面直接进入主题。此处的公式就是:=SUMPRODUCT(1*($A2&$B2&$C2&$D2=$A$2:$A$16&$B$2:$B$16&$C$2:$C$16&$D$2:$D$16))>1这里高亮了来自业务部和财务部的吴晨,明显这是同名不同人。用这个sumproduct函数就很好地解决了这个问题。吴晨不会被高亮为重复的记录。原创 2014-12-21 15:39:05 · 524 阅读 · 0 评论 -
【来日复制粘贴】数据透视表分类不同账龄
大意就是有一些应收账数据,对应着具体日期,现在需要你把这些应收账分为:小于30天的账,31到60天的账,61到90天的账,最后是大于90天的账。数据源接下来第一件事,把它做成数据透视表,如下图。接下来选中某个标题字段,然后就看下图吧。数据透视表选项卡->域、项目和集->计算字段...插入字段以及计算公式。公式留两个。一个账龄大于90天,一个账龄31到60天的。账龄大原创 2015-01-09 20:58:02 · 1991 阅读 · 0 评论 -
【来日复制粘贴】以符号作为换行依据将一个单元格具有分隔符的一串数据变成数排
1. 通过函数公式分行=MID($A$1,FIND("\",SUBSTITUTE(";"&$A$1&";",";","\",ROW(1:1))),FIND("\",SUBSTITUTE(";"&$A$1&";",";","\",ROW(2:2)))-FIND("\",SUBSTITUTE(";"&$A$1&";",";","\",ROW(1:1)))-1)效果:2. 构建内存数据实现分原创 2014-12-13 23:03:33 · 926 阅读 · 0 评论 -
【来日复制粘贴】用高级筛选和函数公式拆分数据列表
要求把表2里面不包含的项从表1取出,并建立一个新表。A25处的公式为=ISNA(MATCH(A3,$F$3:$F$12,0))原创 2014-12-21 11:45:52 · 613 阅读 · 0 评论 -
【来日复制粘贴】使某列筛选的结果映射到标题部分
上图的实现公式=INDEX(A5:A103,MATCH(1,SUBTOTAL(3,OFFSET(A4,ROW(A5:A103)-4,)),))原创 2014-12-20 10:10:40 · 496 阅读 · 0 评论 -
【来日复制粘贴】让PivotTable随数据源一起动起来
在不用刷新按钮的前提下,如何让pivot和数据源一起动起来呢?VBA之。1. 首先要给数据源定义好名称,公式:=OFFSET(数据源!$A$1,,,COUNTA(数据源!$A:$A),COUNTA(数据源!$1:$1))2. 制作pivot时候,引用数据区域要写3. 接下来针对数据源的tab要上vba代码了。Private Sub Worksheet_Activate()原创 2014-12-23 23:09:11 · 4189 阅读 · 0 评论 -
PivotTable之数据源和透视表随意移动更名也能刷新数据
从此以后只要让数据源和查询的数据透视表文件在同一个文件夹里面(可更改文件夹名),那么就可以随意刷新数据,而不必担心需要更换外部数据源路径了。以下为代码(包括注解运作原理)Private Sub Workbook_Open() Dim strCon As String, iPath As String, i As Integer, iFlag As String, iStr As S原创 2015-01-26 23:23:51 · 7556 阅读 · 0 评论 -
【来日复制粘贴】PivotTable之会计月份跨月(不是寻常的1号到30号)
当某公司的会计年度是类似2009-05-05 to 2010-05-04这种不够规律的会计年度,可以用以下两个函数作为辅助做出如下效果的数据透视表。原数据: 效果:年份列的公式=IF(AND(MONTH(A2)=12,DAY(A2)>=26),YEAR(A2)+1,YEAR(A2))月份列公式=IF(AND(MONTH(A2)=12,DAY(A2)>=26)原创 2015-01-07 22:02:30 · 1206 阅读 · 0 评论 -
Match函数的典型用法
辅助列的数组公式为=MATCH(A2&B2&C2,A$2:A$145&B$2:B$145&C$2:C$145,0)原创 2014-12-23 23:07:24 · 1214 阅读 · 0 评论 -
如何用VBA做数据透视表
原数据 (第一行数据有空白格子,最好还是填上0。因为制作原理是它要先选定从a1格子开始一块全部有内容的矩阵,即A1:M2)效果代码Sub Generate()Dim ptcache As PivotCacheDim pt As PivotTableDim prange As RangeSet ws = Sheet1For Each pt In Sheet2.原创 2015-01-08 11:43:54 · 9287 阅读 · 0 评论 -
[Self-made] Text To Rows
This VBA function is a milestone for my learning. Comparing to the first code post in this section, this one does not take much time. Essentially, that first code is more interesting and trickier原创 2015-03-19 09:19:53 · 445 阅读 · 0 评论 -
【理解】如何定时运行程序
以下这个程序非常简单来自书本《Excel2010 VBA 入门与提高》第三章疑难58。这个case很容易让初学者觉得和递归有关,其实不然。具体见下。程序运行效果就是,按start以后A2跳出当前时间,且开始一秒一秒地动。再按end A2就会停下来。上代码前,需要先放一下Application.OnTime的prototype: Application.OnTime(Earlie原创 2015-03-18 14:25:50 · 681 阅读 · 0 评论 -
【来日复制粘贴】跨sheet的数据录入
效果:录入Sheet2:代码的含义需自己点明一下,方便日后用起来瞬间理解它的运行逻辑。Sub haha()Dim lastRow As LongDim lstData As ListObjectDim rngTitle As RangeSet lstData = Sheet2.ListObjects(1)lastRow = lstData.DataBodyR原创 2015-03-22 16:10:26 · 580 阅读 · 0 评论 -
【理解】运用数据透视表制作三栏账
今天学习了三栏账制作出自书本《Excel2010数据透视表应用大全》,要我说在全书中,实现这个事件步骤还是不少的。然而要写一篇小文章的关键在于,对于我个人来说这个三栏账对Excel的公式和功能的应用还是很丰富的,并且很有用。以下就是实现效果,高亮部分是自己觉得需要注明的地方。三个红框是想着重介绍的第一部分,首先讲借贷那个,其实它就是个表格自定义格式的应用,具体公式如下。[>0]原创 2015-02-13 15:49:12 · 1782 阅读 · 0 评论 -
【理解】多筛选字段数据透视表快速分项打印里面的递归用法
没错,最后还是被这个递归,以及各种复杂的名称搞得晕头转向,不得不记录一下理解的过程。方便以后的应用。其实关键不在于实现这个事件,而在于代码的思路。Sub PrintPvtTblblByMultiPageFlds() Dim objPvtTbl As PivotTable Dim objPvtTblFld As PivotField Dim objPvtTblIm As原创 2015-02-05 16:04:09 · 1058 阅读 · 1 评论 -
【来日复制粘贴】使用公式提取数据
要求一. 在以下表格中提取市场部人员的员工编号。可用公式有两个,全部为数组公式=IF(ROW(1:1)>COUNTIF(C$2:C$20,"市场部"),"",INDEX(A$1:A$20,SMALL(IF(C$2:C$20="市场部",ROW($2:$20)),ROW(1:1))))在以上公式里面,可以发现一个额外的知识点就是错误值如果转换成数字,会大过一般的数字。发现点就在SMALL原创 2014-12-24 22:11:04 · 549 阅读 · 0 评论 -
【来日复制粘贴】关于排名
图之,1. 关于效率排名公式为=SUMPRODUCT(1*(B2/C22. 关于不占位业绩排名公式为=SUMPRODUCT((B$2:B$15>=B2)*1/COUNTIF(B$2:B$15,B$2:B$15))原创 2014-12-24 23:39:49 · 495 阅读 · 0 评论 -
vba 控制excel与word
留个基础版,方便以后捡。先是ThisWorkbook文件针对这个工作簿本身的一些默认操作。Option ExplicitPrivate Sub Workbook_Open() '打开工作簿运行'设定整个worksheet为文本格式 Sheets("statement_info").UsedRange.NumberFormat = "@" '默认保留的乘客信息...原创 2019-07-04 12:38:28 · 1282 阅读 · 0 评论