FreeOZ论坛

标题: EXCEL的漂亮小技巧 , VBA学习中 [打印本页]

作者: JunJun2013 时间: 27-5-2014 15:30
标题: EXCEL的漂亮小技巧 , VBA学习中
本帖最后由 JunJun2013 于 19-6-2014 15:48 编辑

求职的时候，说自己具备Advanced Microsoft Office Skills 时，有没有想过究竟哪些方面优于常人？

"会用"和outstanding，差距还是很大的。

我承认Word、Outlook、PowerPoint 潜能开发得极少，OneNote, InfoPath Designer, Pulisher, Share Point干脆就不知道怎么回事。

简单分享几个excel的必备tips吧。欢迎补充。

很多键盘操作，包括Ctrl 和 F 键，（比如妇孺皆知的Ctrl +C Ctrl + V）, 熟悉了的确比鼠标快！！
左手食指点"Tab"键，拇指点"Alt"键，快速切换窗口。

最常用的、加减乘除之外的公式，IF, vlookup(hlookup), sumif, midb, replace, indirect（这个其实不常用）, 等等。

你必须会的功能：sorting排序, filter筛选, group/ungroup组合, hide/unhide隐藏, conditional formating条件格式
                                             paste special (可以只粘贴公式或数值，或可以让数据转体）
                                             read-only, protection(保护单元格不被修改或密码保护某文件）
                                                               find & selection 功能

这些也要尝试：Data Validation(对不符合要求的输入给予警示）， what-if analysis

’ ，这个符号可以把公式变成文字。

15，16楼有一些快速操作的tips.

找出循环应用的公式在哪  formulas /error checking /circular references

Hyperlink, 超链接让信息指向挺灵活的，本文件和跨文件都可以。
跨文件的公式就比较简单，直接打开目标文件，用=就行了, 就是要注意update的情况。
从外部引入数据可以是来自Access, SQLServer, Text, 需要的时候要乐于尝试，注意数值和格式。

Define Name也是快速操作之一，详见25楼。

PivotTable透视表，适合用于分类汇总等。

Macros 宏适合于重复动作，比如每次新开表，需要输入相同的表头，设置同样的字体和颜色，输入同样的footer等。
录制宏的时候要注意是否需要用Use Relative References(选中按钮后小图标显示浅黄亮色），这意味着操作可以运用于任何相对位置单元格。

一些重复性的光标操作，可以直接由系统录制宏（开始和结束时点相应两个按钮就行了），根据需要分配给控件。

复杂过程需要用vba写程序。（22楼起步操作介绍）
通过Visual Basic插入Module或直接命名创造新宏,  编辑好程序后，保存宏，到控件的编辑界面，分配宏。

工具条控件（Form Controls) 比较直观，直接定义，也可以结合宏（用第一个button）。
ActiveX Controls 则更复杂灵活，更适合与VBA写的宏结合使用。
(7,8,9楼图)

具体不懂的，一律问google。

作者: JunJun2013 时间: 27-5-2014 15:36
本帖最后由 JunJun2013 于 27-5-2014 21:27 编辑

点几个大神来指教一下。
@cais 这里有Excel高手吗？

http://www.excel-easy.com/vba/events.html

目前还没开始研究excel vba 和 access vba的区别，但感觉excel的各方面应用都直观一些。

作者: Serin 时间: 27-5-2014 17:33
我只用LibreOffice

作者: JunJun2013 时间: 27-5-2014 17:49

Serin 发表于 27-5-2014 16:33

登录/注册后可看大图

我只用LibreOffice

电子表功能简单得多吧？

作者: Serin 时间: 27-5-2014 17:55

JunJun2013 发表于 27-5-2014 16:49

登录/注册后可看大图

电子表功能简单得多吧？

你说的这些，除了VBA以外，别的都有。

作者: JunJun2013 时间: 27-5-2014 17:59
本帖最后由 JunJun2013 于 27-5-2014 20:15 编辑

Serin 发表于 27-5-2014 16:55

登录/注册后可看大图

你说的这些，除了VBA以外，别的都有。

唉。。。这不就是想要VBA吗？
这个LibreOffice Calc引以为荣的就是一个透视表的样子。

作者: JunJun2013 时间: 27-5-2014 21:20
本帖最后由 JunJun2013 于 30-5-2014 13:53 编辑

选项下显示设计developer界面的设置，一般默认是不显示的。

作者: JunJun2013 时间: 27-5-2014 21:22
如何插入控件

作者: JunJun2013 时间: 27-5-2014 21:25
右键点击，编辑控件属性，格式，输出指向哪个单元格，功能可以逐一尝试，有选择项，滚动条，等

作者: 飞天雪狐 时间: 27-5-2014 22:24
跟着学习

作者: JunJun2013 时间: 27-5-2014 22:26

飞天雪狐发表于 27-5-2014 21:24

登录/注册后可看大图

跟着学习

欢迎啊！
要是来指教就更好了！

作者: 飞天雪狐 时间: 27-5-2014 22:33

JunJun2013 发表于 27-5-2014 19:26

登录/注册后可看大图

欢迎啊！
要是来指教就更好了！

只有学习的份啦。。。

作者: cais 时间: 28-5-2014 06:47

JunJun2013 发表于 27-5-2014 14:36

登录/注册后可看大图

点几个大神来指教一下。
@cais 这里有Excel高手吗？

我也是靠问谷歌同学还有excel本身的帮助的。
Excel自己给的帮助信息也是很详细的。通常如果知道是要用哪个function，看帮助基本上就能解决问题。
不知道用哪个function的时候，可以google一下。

’ ，这个符号可以把公式变成文字。

这个我是第一次看到。没想到Excel也这么lispy啊。

作者: JunJun2013 时间: 28-5-2014 12:18
公司里隐藏着一个VBA高手 :happy

可以上youtube看教学视频，Access all in one ， excel的还需要看看哪个最实用
有那种样本文件可以下载
没有捷径可走，看完20个视频，几百分钟总是要花的

作者: JunJun2013 时间: 29-5-2014 16:52
本帖最后由 JunJun2013 于 3-6-2014 11:56 编辑

快速复制worksheet的办法：
holding down the CTRL key while dragging the sheet tab of the original 按住CTRL，拖动工作表
用右键也能做到，相对慢一些。

选择指定单元格后，光标指向右下角，看到的十字架叫handle,drag这个handle, 就能快速复制，复制后会看到小方格，就是auto fill options, 点击可以选择不同的复制方式
更快速的复制是选定单元格后，按CTRL，双击选中区域下角的黑点，能快速FILL单元格（前提是空缺单元格，即旁边一列已经有数值），若格式（数值会默认为最佳的PATTERN)不满意，可以用auto fill options来改变

用这个可以选定所有的空格
Home > Find & Select > Go To Special…, and a Go To Special dialog box will appear, then check Blanks option.
选定后，对其中一个单元格输入公式或数值，按CTRL+ENTER可以让所有空单元格复制这个公式或数值，若是格式就直接可以批量修改啦，比如上色。

这个go to special还可以找到别的，比如公式部分，go to 则可以指向某个单元格

作者: JunJun2013 时间: 29-5-2014 17:03
本帖最后由 JunJun2013 于 29-5-2014 18:52 编辑

Home > Find & Select 下有个"replace"也挺好用的，试验一下就知道用法，可以把某个数值的单元格替换成另一个
晚上发截屏图

作者: 浮云云艾米莉 时间: 29-5-2014 19:05
以前excel 2003经常写vba. 2010了就不怎么写了，大部分功能都有，实在不行的就导入access 然后在access 做query

作者: JunJun2013 时间: 29-5-2014 19:29
本帖最后由 JunJun2013 于 29-5-2014 18:39 编辑

EmilyS 发表于 29-5-2014 18:05

登录/注册后可看大图

以前excel 2003经常写vba. 2010了就不怎么写了，大部分功能都有，实在不行的就导入access 然后在access 做q ...

你目前的工作，数据不复杂吧？
是啊，那些查询给人感觉足足够了。要是真的复杂用途，又是有软件系统。
不过，偷师Access的query是个好办法
我是说，有很多复杂的语句就从query的SQL里参考一下

其实excel的大部分普通功能都没有被多数人充分利用，VBA给人感觉用处不大
我认为excel编程的主要意义在于大多数人熟悉excel, 会有大量现成的数据，在这个基础上可以做些操作改进，比重新设计access文件简单可行

我朋友让我将她现有的复杂繁琐的excel文件改成access, 优化那些容易出错和重复的地方，我试了一下，很烦，要是涉及到其它部门、外部数据就更烦，毕竟习惯用access的人不多。

我除了想好好学一下之外，更主要的考虑的如何合理充分地利用推广这个功能，否则没有用处，既没有动力，时间久了也会忘记

作者: 浮云云艾米莉 时间: 29-5-2014 22:12

JunJun2013 发表于 29-5-2014 18:29

登录/注册后可看大图

你目前的工作，数据不复杂吧？
是啊，那些查询给人感觉足足够了。要是真的复杂用途，又是有软件系统。 ...

以前工作数据很复杂

现在基本完全不弄了

作者: chingwu 时间: 29-5-2014 23:30
跟着学习 excel学深奥了足以靠这个来找一份reporting的工作了

作者: JunJun2013 时间: 29-5-2014 23:34

chingwu 发表于 29-5-2014 22:30

登录/注册后可看大图

跟着学习 excel学深奥了足以靠这个来找一份reporting的工作了

我准备花三个月时间。
现在就是提炼一下小tips, 热热身

excel vba 编程最大的现状是普及度很低，是机会、也有做无用功的可能，适合财务，分析，金融业等人士

无论如何，宏和控件功能值得尝试开发。

跟帖互相学习的同学请督促我。

作者: JunJun2013 时间: 30-5-2014 14:43
本帖最后由 JunJun2013 于 30-5-2014 22:25 编辑

Add-in 没什么大用处，不仔细研究了。

Go file/options/add-ins, 选中需要的add-in, 左小角的Manage旁边选"Excel add-ins"，然后点"go"

创建新宏：
可以通过录制record macros（直观操作）或者VBA来实现。宏完成后保存文件时会提示保存成macro enable模式。

用VBA创建宏，两种方法：
1. 点macros按钮，输入新的名字name，点“Create"，就会显示Visual Basic界面(图1)。
可以看到：Sub name()
End Sub

2. 直接点开Visual Basic, 在VBAProject下Insert Module, 记得要指向正确的目标Workbook, 开始写Procedure, 在Sub字样后输入名字，再编写程序主题，退出会自动保存为宏(图2）

修改或删除宏，也可以通过点按钮"macros"（"edit", "delete") 或进入Visual Basic/VBAProject/Modules (修改：双击，删除：File->Remove Module*）

关于文件种类：
普通文件Excel文件后缀是xls
带有add-in的是xlam
带有宏的是xlsm

在设计的时候，宏安全选择设置为”enable all macros(不推荐)"。

作者: savageliang 时间: 30-5-2014 15:18

Serin 发表于 27-5-2014 16:33

登录/注册后可看大图

我只用LibreOffice

国内的WPS也很好，对个人免费，用了很长时间，除了Word中的table有点问题，dictionary只支持美国英语，Excel中的数据透视有点刷新的问题，个人感觉其它方面都不错。

另外，在iPad上也是免费的，不像微软的Office 365在iPad上只能看不能编辑（编辑得花钱）。

作者: 浮云云艾米莉 时间: 30-5-2014 21:42
本帖最后由 EmilyS 于 30-5-2014 20:44 编辑

JunJun2013 发表于 29-5-2014 18:29

登录/注册后可看大图

你目前的工作，数据不复杂吧？
是啊，那些查询给人感觉足足够了。要是真的复杂用途，又是有软件系统。 ...

现在Excel用的最多的就是filter/sorting + 各种vlookup + 写ms batch script + hyperlinking

最喜欢定义一个格子或者定义一个selection，然后一直用这个名字来refer

ctrl + shift + 上、下箭头是我最喜欢的shortcut

作者: JunJun2013 时间: 2-6-2014 12:09
谢谢EmilyS, 这个Define Name(Formulas Tab)挺好的，可以快速选定或者在公式里引用。
http://www.dummies.com/how-to/co ... -in-excel-2010.html
http://office.microsoft.com/en-a ... as-HA010342417.aspx

可以请你稍微说说怎么写batch script吗？谢谢啦！

作者: ashanadsl 时间: 2-6-2014 12:31
提示: 作者被禁止或删除, 无法发言

作者: tingnishuo 时间: 2-6-2014 12:50
本帖最后由 tingnishuo 于 2-6-2014 12:09 编辑

哇！好高深的！

求教下：计算出来的一个价格，小数点后保留两位，需要按下列规则进位，如何在excel里面实现：

1，如果最后一位是0 或者5，保持不变，例如：12.50， 17.85；
2，如果最后一位是8、9、或1、 2，向上或向下进为0。如22.88为22.90， 15.11为15.10
3，如果最后一位为6、7或3、4，向下或向上进位为5；如56.56为56.56，34.34为34.35

作者: JunJun2013 时间: 2-6-2014 13:04

tingnishuo 发表于 2-6-2014 11:50

登录/注册后可看大图

哇！好高深的！

求教下：计算出来的一个价格，小数点后保留两位，需要按下列规则进位，如何在excel里面实 ...

56.56 应该进（退）为56.55吧？跟雅思打分似的，是四舍五入的一种，用公式可以实现，但肯定有简洁版和啰嗦版。我试一下。也许VBA的条件STATEMETN可以做得容易操作点，这不还没正式学呢。

作者: tingnishuo 时间: 2-6-2014 13:08

JunJun2013 发表于 2-6-2014 12:04

登录/注册后可看大图

56.56 应该进（退）为56.55吧？跟雅思打分似的，是四舍五入的一种，用公式可以实现，但肯定有简洁版和啰嗦 ...

嘻嘻，是笔误啦，去改过来～～谢谢指正～

作者: JunJun2013 时间: 2-6-2014 13:14
本帖最后由 JunJun2013 于 2-6-2014 12:19 编辑

tingnishuo 发表于 2-6-2014 11:50

登录/注册后可看大图

哇！好高深的！

求教下：计算出来的一个价格，小数点后保留两位，需要按下列规则进位，如何在excel里面实 ...

我用最傻的公式办法可以实现,在右边加上三列。
A          B                C                                  D
56.56    =A2/0.05    =ROUND(E2,0)                =F2*0.05=56.55

把不需要列隐藏起来。
30.48    609.60 610    30.50
29.01    580.20 580    29.00
18.07    361.40 361    18.05
19.00    380.00 380    19.00
26.03    520.60 521    26.05
56.56    1,131.20 1131       56.55

作者: tingnishuo 时间: 2-6-2014 14:36

JunJun2013 发表于 2-6-2014 12:14

登录/注册后可看大图

我用最傻的公式办法可以实现,在右边加上三列。
A B C ...

谢谢谢谢！真是太好了！

作者: JunJun2013 时间: 2-6-2014 17:39
本帖最后由 JunJun2013 于 3-6-2014 13:56 编辑

通过系统录制宏后，点编辑, 可以参看宏的VBA语言，可以复制应用到新的程序中，这个是最实用的上手办法了。
毕竟那么多功能和定义，一下子都掌握难。

一个最基本的程序是这样的：

- 用撇号描述程序的内容,比如 ‘this is for testing
- 定义变量，比如dim total as long(或者integer)
- 功能或任务，最基本的： +，-，*，/，=，<=,>=,<>, &, msgbox, msgbox(msg, vbYesNo) ，" " 给予文本数值，# # 给予日期值，比如#03/06/2014#
还有这个^,表示求幂，计算优先顺序同常理，用（）来改变默认优先顺序
- 结构: 比如，If-Then-Else 或者 loop

不用管大小写和空格，回车后自动修正。

例1：--运行这个可以得到101，这里的结构就是loop
Sub testing()
'testing
Dim total As Long, i As Long
total = 0
For i = 0 To 100
total = total + 1
Next i
MsgBox total
End Sub

例2：--询问对话框，这个例子中有关于信息框和如何分配单位格数值的表达（入门必备）。
Sub msgtesting()
Msg = "is your name " & Application.Workbooks("test.xlsm").Worksheets("sheet1").Range("A1") & "?"
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbNo Then
MsgBox "oh, never mind"
Else: MsgBox "I must be clairvoyang"
End If
End Sub

作者: windwing00 时间: 2-6-2014 18:44
Excel的宏功能类似于Visual C++的MFC（可视化编程），比较简单也很好用，需要有点编程基础的，我以前编过从一个文件夹下面的400-500个excel表格里面提取数据到一个excel表里面。

作者: JunJun2013 时间: 2-6-2014 19:53

windwing00 发表于 2-6-2014 17:44

登录/注册后可看大图

Excel的宏功能类似于Visual C++的MFC（可视化编程），比较简单也很好用，需要有点编程基础的，我以前编过从 ...

是的，有点基础会有基本的概念和兴趣。其实任何人有足够耐心也是可以的，就是普遍没有兴趣。
我现在面临的是逐一了解众多的理论（目前思路还挺混乱的，只会最简单的程序），然后如何应用到工作中，的确挺费时间的。对于文件数目多，数据复杂，宏的优势就显出来了。
你介意分享一下你曾经编过的吗？贴出部分你认为漂亮的procedure/function(隐去敏感信息）。谢谢。

作者: TJLADY 时间: 2-6-2014 21:59
支持楼主！
我们公司里的一些数据库和Datasheet都需要VBA开发，公司里有一些业余高手，我最近也开始感兴趣

作者: windwing00 时间: 2-6-2014 22:54
这个功能看起来好像很复杂高效率，实际上也很简单的，就是懂得读取数据的命令和每个函数的参数是什么，VBA和MFC都是趋向于模块化的编程手段，搞清楚输入、输出和衔接好各个模块之间关系的话就好多了。我现在也好久没做过编程了，已经脱离这块很久了，转行做了通信施工设计，发现到这里工作很难找，不如做软件工程师好找工作。

作者: JunJun2013 时间: 3-6-2014 15:09
标题: 逻辑运算符：
本帖最后由 JunJun2013 于 3-6-2014 14:37 编辑

Not Performs a logical negation on an expression.
And Performs a logical conjunction on two expressions.
Or Performs a logical disjunction on two expressions.
Xor Performs a logical exclusion on two expressions.
Eqv Performs a logical equivalence on two expressions.
Imp Performs a logical implication on two expressions.
Is Compares two object variables
Like Compares two strings by using wildcard characters

作者: JunJun2013 时间: 3-6-2014 15:40
标题: VBA的陈述语言一览表
Statement Action
AppActivate Activates an application window
Beep Sounds a tone via the computer’s speaker
Call Transfers control to another procedure
ChDir Changes the current directory
ChDrive Changes the current drive
Close Closes a text file
Const Declares a constant value
Date Sets the current system date
Declare Declares a reference to an external procedure in a Dynamic Link Library (DLL)
DefBool Sets the default data type to Boolean for variables that begin with specified letters
DefByte Sets the default data type to Byte for variables that begin with specified letters
DefCur Sets the default data type to Currency for variables that begin with specified
letters
DefDate Sets the default data type to Date for variables that begin with specified letters
DefDec Sets the default data type to Decimal for variables that begin with specified letters
DefDbl Sets the default data type to Double for variables that begin with specified letters
DefInt Sets the default data type to Integer for variables that begin with specified letters
DefLng Sets the default data type to Long for variables that begin with specified letters
DefObj Sets the default data type to Object for variables that begin with specified letters
DefSng Sets the default data type to Single for variables that begin with specified letters
DefStr Sets the default data type to String for variables that begin with specified letters
DefVar Sets the default data type to Variant for variables that begin with specified letters
DeleteSetting Deletes a section or key setting from an application’s entry in the Windows Registry
Dim Declares variables and (optionally) their data types
Do-Loop Loops through a set of instructions
End Used by itself, exits the program; also used to end a block of statements that
begin with If, With, Sub, Function, Property, Type, or Select
Enum Declares a type for enumeration
Erase Re-initializes an array
Error Simulates a specific error condition
Event Declares a user-defined event
Exit Do Exits a block of Do-Loop code
Exit For Exits a block of For-Next code
Exit Function Exits a Function procedure
Exit Property Exits a property procedure
Exit Sub Exits a subroutine procedure
FileCopy Copies a file
For Each-Next Loops through a set of instructions for each member of a series
For-Next Loops through a set of instructions a specific number of times
Function Declares the name and arguments for a Function procedure
Get Reads data from a text file
GoSub...Return Branches to and returns from a procedure
GoTo Branches to a specified statement within a procedure
If-Then-Else Processes statements conditionally
Implements Specifies an interface or class that will be implemented in a class module
Input # Reads data from a sequential text file
Kill Deletes a file from a disk
Let Assigns the value of an expression to a variable or property
Line Input # Reads a line of data from a sequential text file
Load Loads an object but doesn’t show it
Lock...Unlock Controls access to a text file
Lset Left-aligns a string within a string variable
Mid Replaces characters in a string with other characters
MkDir Creates a new directory
Name Renames a file or directory
On Error Gives specific instructions for what to do in the case of an error
On...GoSub Branches, based on a condition
On...GoTo Branches, based on a condition
Open Opens a text file
Option Base Changes the default lower limit for arrays
Option Compare Declares the default comparison mode when comparing strings
Option Explicit Forces declaration of all variables in a module
Option Private Indicates that an entire module is Private
Print # Writes data to a sequential file
Private Declares a local array or variable
Property Get Declares the name and arguments of a Property Get procedure
Property Let Declares the name and arguments of a Property Let procedure
Property Set Declares the name and arguments of a Property Set procedure
Public Declares a public array or variable
Put Writes a variable to a text file
RaiseEvent Fires a user-defined event
Randomize Initializes the random number generator
ReDim Changes the dimensions of an array
Rem Specifies a line of comments (same as an apostrophe [‘])
Reset Closes all open text files
Resume Resumes execution when an error-handling routine finishes
RmDir Removes an empty directory
RSet Right-aligns a string within a string variable
SaveSetting Saves or creates an application entry in the Windows Registry
Seek Sets the position for the next access in a text file
Select Case Processes statements conditionally
SendKeys Sends keystrokes to the active window
Set Assigns an object reference to a variable or property
SetAttr Changes attribute information for a file
Static Declares variables at the procedure level so that the variables retain their values as long as the code is running
Stop Pauses the program
Sub Declares the name and arguments of a Sub procedure
Time Sets the system time
Type Defines a custom data type
Unload Removes an object from memory
While...Wend Loops through a set of instructions as long as a certain condition remains true
Width # Sets the output line width of a text file
With Sets a series of properties for an object
Write # Writes data to a sequential text file

作者: JunJun2013 时间: 3-6-2014 15:42
标题: VBA的function一览表
本帖最后由 JunJun2013 于 3-6-2014 14:47 编辑

EXCEL中fx函数可能不包括在这个清单里，可以直接引用，比如：
Deg = Application.WorksheetFunction.Degrees(3.14)
或者简写为：Deg = Application.Degrees(3.14)

Abs Returns the absolute value of a number
Array Returns a variant containing an array
Asc Converts the first character of a string to its ASCII value
Atn Returns the arctangent of a number
CallByName Executes a method, or sets or returns a property of an object
CBool Converts an expression to a Boolean data type
CByte Converts an expression to a Byte data type
CCur Converts an expression to a Currency data type
CDate Converts an expression to a Date data type
CDbl Converts an expression to a Double data type
CDec Converts an expression to a Decimal data type
Choose Selects and returns a value from a list of arguments
Chr Converts a character code to a string
CInt Converts an expression to an Integer data type
CLng Converts an expression to a Long data type
Cos Returns the cosine of a number
CreateObject Creates an Object Linking and Embedding (OLE) Automation object
CSng Converts an expression to a Single data type
CStr Converts an expression to a String data type
CurDir Returns the current path
CVar Converts an expression to a variant data type
CVDate Converts an expression to a Date data type (for compatibility, not recommended)
CVErr Returns a user-defined error value that corresponds to an error number
Date Returns the current system date
DateAdd Adds a time interval to a date
DateDiff Returns the time interval between two dates
DatePart Returns a specified part of a date
DateSerial Converts a date to a serial number
DateValue Converts a string to a date
Day Returns the day of the month of a date
DDB Returns the depreciation of an asset
Dir Returns the name of a file or directory that matches a pattern
DoEvents Yields execution so the operating system can process other events
Environ Returns an operating environment string
EOF Returns True if the end of a text file has been reached
Error Returns the error message that corresponds to an error number
Exp Returns the base of natural logarithms (e) raised to a power
FileAttr Returns the file mode for a text file
FileDateTime Returns the date and time when a file was last modified
FileLen Returns the number of bytes in a file
Filter Returns a subset of a string array, filtered
Fix Returns the integer portion of a number
Format Displays an expression in a particular format
FormatCurrency Returns an expression formatted with the system currency symbol
FormatDateTime Returns an expression formatted as a date or time
FormatNumber Returns an expression formatted as a number
FormatPercent Returns an expression formatted as a percentage
FreeFile Returns the next available file number when working with text files
FV Returns the future value of an annuity
GetAllSettings Returns a list of settings and values from the Windows Registry
GetAttr Returns a code representing a file attribute
GetObject Retrieves an OLE Automation object from a file
GetSetting Returns a specific setting from the application’s entry in the Windows Registry
Hex Converts from decimal to hexadecimal
Hour Returns the hour of a time
IIf Evaluates an expression and returns one of two parts
Input Returns characters from a sequential text file
InputBox Displays a box to prompt a user for input
InStr Returns the position of a string within another string
InStrRev Returns the position of a string within another string from the end of the string
Int Returns the integer portion of a number
IPmt Returns the interest payment for a given period of an annuity
IRR Returns the internal rate of return for a series of cash flows
IsArray Returns True if a variable is an array
IsDate Returns True if a variable is a date
IsEmpty Returns True if a variable has not been initialized
IsError Returns True if an expression is an error value
IsMissing Returns True if an optional argument was not passed to a procedure
IsNull Returns True if an expression contains a Null value
IsNumeric Returns True if an expression can be evaluated as a number
IsObject Returns True if an expression references an OLE Automation object
Join Combines strings contained in an array
LBound Returns the smallest subscript for a dimension of an array
LCase Returns a string converted to lowercase
Left Returns a specified number of characters from the left of a string
Len Returns the number of characters in a string
Loc Returns the current read or write position of a text file
LOF Returns the number of bytes in an open text file
Log Returns the natural logarithm of a number
LTrim Returns a copy of a string with no leading spaces
Mid Returns a specified number of characters from a string
Minute Returns the minute of a time
MIRR Returns the modified internal rate of return for a series of periodic cash flows
Month Returns the month of a date as a number
MonthName Returns the month of a date as a string
MsgBox Displays a modal message box
Now Returns the current system date and time
NPer Returns the number of periods for an annuity
NPV Returns the net present value of an investment
Oct Converts from decimal to octal
Partition Returns a string representing a range in which a value falls
Pmt Returns a payment amount for an annuity
Ppmt Returns the principal payment amount for an annuity
PV Returns the present value of an annuity
QBColor Returns a red/green/blue (RGB) color code
Rate Returns the interest rate per period for an annuity
Replace Returns a string in which a substring is replaced with another string
RGB Returns a number representing an RGB color value
Right Returns a specified number of characters from the right of a string
Rnd Returns a random number between 0 and 1
Round Returns a rounded number
RTrim Returns a copy of a string with no trailing spaces
Second Returns the seconds portion of a specified time
Seek Returns the current position in a text file
Sgn Returns an integer that indicates the sign of a number
Shell Runs an executable program
Sin Returns the sine of a number
SLN Returns the straight-line depreciation for an asset for a period
Space Returns a string with a specified number of spaces
Spc Positions output when printing to a file
Split Returns a one-dimensional array containing a number of substrings
Sqr Returns the square root of a number
Str Returns a string representation of a number
StrComp Returns a value indicating the result of a string comparison
StrConv Returns a converted string
String Returns a repeating character or string
StrReverse Returns a string, reversed
Switch Evaluates a list of Boolean expressions and returns a value associated with the first
True expression
SYD Returns the sum-of-years’ digits depreciation of an asset for a period
Tab Positions output when printing to a file
Tan Returns the tangent of a number
Time Returns the current system time
Timer Returns the number of seconds since midnight
TimeSerial Returns the time for a specified hour, minute, and second
TimeValue Converts a string to a time serial number
Trim Returns a string without leading spaces and/or trailing spaces
TypeName Returns a string that describes the data type of a variable
UBound Returns the largest available subscript for a dimension of an array
UCase Converts a string to uppercase
Val Returns the number formed from any initial numeric characters of a string
VarType Returns a value indicating the subtype of a variable
Weekday Returns a number indicating a day of the week
WeekdayName Returns a string indicating a day of the week
Year Returns the year of a date

作者: JunJun2013 时间: 4-6-2014 15:31
本帖最后由 JunJun2013 于 4-6-2014 16:57 编辑

对比较复杂的任务，又还不知道如何编写程序，可以通过系统录制数个宏，然后把这些宏合并到一个（在宏里引用另一个宏）
Application.Run "filename.xlsm!macroname"

也可以参考这些宏的语句，在编辑功能下复制黏贴或做相应修改，达到目的。

所有的基本功能，都可以通过录制宏来参考学习，这样，就开始编程了。

接下来会把最常规表达（命令）提炼出来一个清单，再学习一些代表性的综合程序实例。

最重要的还是要结合实践，要有具体的任务（必不可少），才有学习和应用的动力。我已经成功地减少了好多重复工作了。

另外上传一份VBA command 的full list供参考，对word, access也适用。

[attach]309147[/attach]

作者: JunJun2013 时间: 4-6-2014 17:22
本帖最后由 JunJun2013 于 10-6-2014 14:44 编辑

一些常规code:

定义变量，dim var as 空格后有很多选择
变量可是是整数，货币金额，日期，窗口，工作表/簿，一片单元格范围，等

打开文件：
ChDir "C:\foldername\foldername"
Workbooks.Open Filename:="C:\foldername\foldername\\filename.xls"

已经打开的文件，选中或激活(即光标进入，出于待编辑状态）
Windows("filename.xlsm").Activate

关闭开着的文件
Windows("filename.xls").Activate
ActiveWindow.Close
如果已经在active的状态，就直接 ActiveWindow.Close
ActiveWorkbook.close

移动上下或左右滚动条，改变表格在屏幕上的显示位置
这个是表示移动列，或行
ActiveWindow.ScrollColumn = 2 （这个数字表示几列，若是2，则C在最左边）
ActiveWindow.ScrollRow = 2 （这个数字表示几行，若是2，则row3在最上边）

用这个也可以，表示向右或下移，相当于移动滚动条旁的小箭头
ActiveWindow.SmallScroll ToRight:=8
ActiveWindow.SmallScroll Down:=4

选择某个或某些单元格
Range("C2:F16").Select
这个表示实际的位置

ActiveCell.Range("A1:F16").Select
这个表示从某个选中单元格起，相对位置

或者需要移动光标，括号第一个数表示行的移动，第二个数表示列的移动，负数表示反向移动
ActiveCell.Offset(-1, 0).Range("A1:C11").Select

ActiveCell.Offset(0, 7).Columns("A:C").EntireColumn.Select
ActiveCell.Offset(-10, 0).Rows("1:4").EntireRow.Select

这个表示某个单元格的相对位置，第一个表示从相对向右第8列起全选3列，第2个表示相对退回（向上，列则是向左）10列开始全选4行。

如果是实际位置（绝对值），就Columns("A:C").Select 或 Rows("3:4").Select， EntireColumn.加不加都可以

选定位置后，复制和粘贴
Selection.Copy
ActiveSheet.Paste

作者: JunJun2013 时间: 5-6-2014 15:45
本帖最后由 JunJun2013 于 10-6-2014 15:42 编辑

Application Visual Basic/View/Object Brower 可以看所有应用功能

简单应用例子:

1. 取值
var = Application.Workbooks("filename.xlsm").Worksheets("sheetname").Range("A1")

2. 执行函数
Range("A3").Value = Application.WorksheetFunction.Average(Range("A1:A2"))

3. 在执行关闭或者替换等动作时，系统会警示，需要手工点是或否，可以关闭警示，直接执行（在ACCESS中也一样）。记得要再打开。

Application.DisplayAlerts = False
ActiveWorkbook.Close （例）
Application.DisplayAlerts = True

4. 循环操作时，可关闭显示更新，使得运行速度更快，如：
Dim i As Integer
Application.ScreenUpdating = False
For i = 1 To 10000
Range("A1").Value = i
Next i
Application.ScreenUpdating = True

5.With 的用法举例--就是省去重复的语句
With Selection.Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 12611584
      .TintAndShade = 0
      .PatternTintAndShade = 0
End With

6.Set, 设定某个值
比如 Dim InputArea As Range
Set InputArea = Range(“C16:E16”)

7. 单元格工作表工作薄的性质，可以给值，用 = 来表示
当面的：activecell.value, activesheet.name, activecell.font, activeworkbook.name, ActiveCell.FormulaR1C1（一行一列）等等，也包括上面例子的填充色等
公式的用法：ActiveCell.FormulaR1C1 = "='[test.xlsm]Sheet5'!R6C2" （第6行第2列），这个[ ]和 ! 是被取值的单元格的表达
也可以用selection（就是前面执行过选取区域功能了）或者直接输入目标名字来表达
比如，Application.Workbooks("test.xlsm").Worksheets("sheet5").Range("A1").Value = 100 这里application加不加都可以

8. _下划线分段

9.  这个例子就是手动输入某个值的对话框，Username = InputBox("Enter your name")

作者: JunJun2013 时间: 10-6-2014 15:58
本帖最后由 JunJun2013 于 10-6-2014 16:13 编辑

For Next Loop 的用法, 32楼还有一个For Next的用法，同时简单If Then的用法也在32楼，不再举例
Dim Item As Worksheet
For Each Item In ActiveWorkbook.Worksheets
MsgBox Item.Name
Next Item

GoTo 的用法
Sub atest()
Username = InputBox("Enter your name")
If Username <> "Jack" Then GoTo WrongName
MsgBox "Welcome"
Exit Sub
WrongName:
MsgBox "Sorry"
End Sub

If Then Else的用法，如果多重条件，也可以用ElseIf, 在条件符合的时候直接退出程序。多重条件建议用Select Case结构
Sub GreetMe6()
If Time < 0.5 Then
MsgBox “Good Morning”
Else
If Time >= 0.5 And Time < 0.75 Then
MsgBox “Good Afternoon”
Else
If Time >= 0.75 Then
MsgBox “Good Evening”
End If
End If
End If
End Sub

Select Case的用法--测试了一下，非常好用！
Sub Discount3()
Dim Quantity As Variant
Dim Discount As Double
Quantity = InputBox(“Enter Quantity: “)
Select Case Quantity
Case “”
Exit Sub
Case 0 To 24
Discount = 0.1
Case 25 To 49
Discount = 0.15
Case 50 To 74
Discount = 0.2
Case Is >= 75
Discount = 0.25
End Select
MsgBox “Discount: “ & Discount
End Sub
简洁Select Case的例子：
Sub GreetUser2()
Select Case Weekday(Now)
Case 2, 3, 4, 5, 6
MsgBox “This is not the weekend”
Case Else
MsgBox “This is the weekend”
End Select
End Sub

Page 231

作者: JunJun2013 时间: 12-6-2014 15:59
本帖最后由 JunJun2013 于 12-6-2014 16:14 编辑

Sub OpenFile

Dim enterdate As Date
Dim MyFile As String
Dim fname As String

enterdate = InputBox("Enter Date")
fname = Day(enterdate) & Month(enterdate) & Year(enterdate)
Const MyFolder = "K:\General Office\Costings\Weekly Costing\"
MyFile = MyFolder & fname & "*.xls"

Workbooks.Open FileName:=MyFile

End Sub

输入日期后，不能打开，事实上在指定目录下是有按日期命名的文件，比如1262014，msgbox测试显示fname的时候也是对的，不知道哪里出错？头晕~~
@windwing00 麻烦百忙之中看一下，谢谢！这个没技术含量，就是不知道哪个细节不对。

@TJLADY 能否也看一下？还有哪位大师？在GOOGLE上看半天，真是头晕呀。。。

尝试了简洁版本：
Sub test
sPath = "\General Office\Costings\Weekly Costing\"
sfile = InputBox("Please Enter Date", "Enter Date", Date)
sfile = Format(sfile, "ddmmyy") & ".xlsx"
Workbooks.Open Filename:=sPath & sfile
End Sub

成功！

作者: 愚夫 时间: 12-6-2014 17:07
Excel功能确实很强大，但感觉很复杂；如果要做数据处理，我还是喜欢导入Access或者Database, 用SQL去实现，然后用Excel来实现报表输出。

作者: JunJun2013 时间: 12-6-2014 17:12

doreadme 发表于 12-6-2014 16:07

登录/注册后可看大图

Excel功能确实很强大，但感觉很复杂；如果要做数据处理，我还是喜欢导入Access或者Database, 用SQL去实现， ...

主要是针对每个同事都在用EXCEL的现状
不能要求他们都会使用ACCESS, 而要处理他们共享出来的信息，就得处理EXCEL的数据
ACCESS的SQL类似于EXCEL里的公式，是微观操作，VBA的优势是宏操作，针对跨文件
不过我才刚入门，欢迎你多提意见

作者: 愚夫 时间: 12-6-2014 17:13

JunJun2013 发表于 12-6-2014 16:12

登录/注册后可看大图

主要是针对每个同事都在用EXCEL的现状
不能要求他们都会使用ACCESS, 而要处理他们共享出来的信息，就得处 ...

你这个帖子很好，我大力支持。

作者: JunJun2013 时间: 12-6-2014 17:17

doreadme 发表于 12-6-2014 16:13

登录/注册后可看大图

你这个帖子很好，我大力支持。

EXCEL的VBA学好后，打算再深造一下ACCESS（原来只会最简单的程序），WORD也可以应用宏。用熟了就觉得蛮好玩的，可以玩文字数字游戏

作者: 阿斯巴田 时间: 13-6-2014 18:04
留下名，以后工作有难题，来这里请教。
别说我懒，实在是有些功能没用到，真没动力和兴趣。

小会计，用用VLOOKUP，IF，SORTING，FILTER差不多就够用了，有时也导入ACCESS，QUERY一下，再导出

作者: JunJun2013 时间: 13-6-2014 19:58

阿斯巴田发表于 13-6-2014 17:04

登录/注册后可看大图

留下名，以后工作有难题，来这里请教。
别说我懒，实在是有些功能没用到，真没动力和兴趣。

会计是最需要EXCEL/ACCESS的职业之一
理论上用公式和查询就能满足数据需求了，有些快速的功能能带来有意义的效果，比如有人等在旁边看你取数，或者在电话中，有些功能减少出错和重复厌烦感，增加一些乐趣
我强烈建议多应用宏功能
欢迎你来提建议，分享好点子，探讨难题，一起学习

作者: JunJun2013 时间: 18-6-2014 13:15
关于控件：
很多有灵气的用法，建议多多尝试

且说option control的用法，Form Controls 和ActiveX Controls,后者直接对应写CODE，前面也可以分配宏（右键操作），后者需要转换设计模式，但没有本质区别。

好处是前者对应Form Controls里面的group,可以后者把选择归为不同的组，操作的时候直接画框就行，然后可以做多个单项选择题
对于多选，似乎只有用CHECK BOX,还需要多研究，目前还没碰到用户需求。

关于日期公式

44楼的巧妙用法之外，还有：
DateAdd ( interval, number, date )
Value Explanation
yyyy Year
q Quarter
m Month
y Day of the year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second

http://www.techonthenet.com/excel/formulas/dateadd.php

非常实用！

另外，再次表示用录制宏可以完成各种基本操作的CODE, 完全不需要去记，最关键是理解需求，然后巧妙组合！

作者: JunJun2013 时间: 19-6-2014 16:45
左手食指点"Tab"键，拇指点"Alt"键，快速切换窗口。

还有很多键盘操作，包括Ctrl 和 F 键，（比如妇孺皆知的Ctrl +C Ctrl + V）, 熟悉了的确比鼠标快！！

作者: mason00 时间: 19-6-2014 16:50

作者: MICHELLE07 时间: 20-6-2014 13:41
有点担心微软的OFFICE的将来发展趋势
居然很多人都不用的，除了财务人员，其他人估计都不需要那些强大的功能

作者: MICHELLE07 时间: 25-9-2014 13:14
直接链接到网站摘取数据的code，非常实用，可以用于汇率，天气预报等。

Private Sub CommandButton1_Click()

Dim WS As Worksheet: Set WS = ActiveSheet

Dim Req As New XMLHTTP

Req.Open "GET", "http://themoneyconverter.com/rss-feed/AUD/rss.xml", False

Req.send

Dim Resp As New DOMDocument

Resp.LoadXML Req.responseText

Dim Item As IXMLDOMNode
For Each Item In Resp.getElementsByTagName("item")
If Item.SelectNodes("title")(0).Text = "USD/AUD" Then
      WS.Range("b1") = Item.SelectNodes("pubDate")(0).Text
      WS.Range("b2") = Item.SelectNodes("description")(0).Text

End If
Next Item
End Sub

作者: huluhzl 时间: 26-9-2014 01:36
进来学习下EXCEL表的使用方法。。。

作者: JunJun2013 时间: 18-11-2014 11:21
修复链接

注意利用name range（在formulas/define name) 的功能，然后可以管理 Formulas / Name Manager

http://www.itbdigital.com/tools- ... mp;Segment=The+Rest

作者: 语之玫瑰 时间: 18-11-2014 12:03
Excel功能强大啊，不过在工作上用效率略低，家庭用特别好

作者: JunJun2013 时间: 18-11-2014 12:10
本帖最后由 JunJun2013 于 18-11-2014 12:16 编辑

语之玫瑰发表于 18-11-2014 12:03
Excel功能强大啊，不过在工作上用效率略低，家庭用特别好

你的工作性质不同，可能感觉不一样，对从事分析工作的人，EXCEL作为系统软件的补充，非常好用，既直观灵活又潜能十足。

家庭用？我家里没有OFFICE....一般家里就在云端写写文章，邮件，和数据有关的私事，都是在公司里完成，嘿嘿。。。

作者: 语之玫瑰 时间: 18-11-2014 12:16

JunJun2013 发表于 18-11-2014 12:10
你的工作性质不同，可能感觉不一样，对从事分析工作的人，EXCEL非常适合，既直观灵活又潜能十足。

家 ...

我以前公司就用Excel的，神器啊！不过数据量一大速度就有点跟不上了

作者: JunJun2013 时间: 18-11-2014 12:24

语之玫瑰发表于 18-11-2014 12:16
我以前公司就用Excel的，神器啊！不过数据量一大速度就有点跟不上了

嗯，EXCEL适合小到中等数据量，甚至文字，半手工，菜鸟员工和爱玩数字的大神，等。。。

就大规模数据来说，我们是软件为主，ACCESS为辅，最后是EXCEL。我制作 ACCESS PROGRAM比较多，后来发现EXCEL 和WORD中的VBA与ACCESS境界不同，所以也就随手看看。

它们都有自己的速成器，也都有很大的功能空间，我这种没有特别钻研精神的人，一直没去开发。。。怎么说呢，一般的情况，还是有效率有乐趣的

作者: mmxx 时间: 28-11-2014 10:55
我最大的问题是：一段时间不用的公式和技巧，就忘了。
也就是vlookup和pivot table用的太多，可能不会太容易忘。

作者: JunJun2013 时间: 28-11-2014 11:09
本帖最后由 JunJun2013 于 28-11-2014 14:39 编辑

mmxx 发表于 28-11-2014 10:55
我最大的问题是：一段时间不用的公式和技巧，就忘了。
也就是vlookup和pivot table用的太多，可能不会太容 ...

是的，我设计好的宏，时间长了，有些小问题，去修改，感觉很费劲。还不如重做一个容易。

一些公式也因为不太用，虽然当时试验下来，会用，但转身就忘了。--比如那个自动复制全部空格的功能。
写这个帖也有备查的目的。

有些功能看各人习惯，比如name range，有些人觉得好用，常用，而我不习惯，试了一下，虽然有趣，但没有去运应用，所以还是生疏。

我个人推荐多多开发、熟练键盘功能，既快又酷。不过我自己还是依赖鼠标。

作者: mmxx 时间: 28-11-2014 13:55
６、７年前刚刚接触excel的时候，有一个专门针对宏的病毒。之后就谈宏色变，没怎么研究过。感觉有点programming的意思。
会用宏的人我都是崇拜滴。
我也超级喜欢热键，酷就一个字。曾经靠特殊粘贴的热键组合，秒杀一个面试官。

作者: MICHELLE07 时间: 30-6-2015 14:16
把一个实例贴一下，以后参考用
Sub Costing()
Dim sfile As Date
Application.DisplayAlerts = False
sPath = "\\server01\Admin_Data\General Office\Angel\taz Costings\Weekly Costing\"
sfile = InputBox("Please Enter Week End Date", "Enter Date", Date) 'Friday
Masterfile = Format(sfile, "yyyymmdd")
sfile0 = sfile
sfile1 = DateAdd("d", -1, sfile) 'Thursday
sfile2 = DateAdd("d", -2, sfile) 'Wednesday
sfile3 = DateAdd("d", -3, sfile) 'Tuesday
sfile4 = DateAdd("d", -4, sfile) 'Monday

sfile0 = Format(sfile0, "ddmmyy")
sfile1 = Format(sfile1, "ddmmyy")
sfile2 = Format(sfile2, "ddmmyy")
sfile3 = Format(sfile3, "ddmmyy")
sfile4 = Format(sfile4, "ddmmyy")

Application.ScreenUpdating = False

If Worksheets("Livestock").CheckBox1.Value = True Then
Workbooks.Open Filename:=sPath & sfile0 & ".xlsx"
Windows(sfile0 & ".xlsx").Activate 'choose Friday file
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A8").Select
ActiveCell.FormulaR1C1 = sfile 'add date
Range("A8").Select
Selection.AutoFill Destination:=Range("A8:A344"), Type:=xlFillCopy
Range("A8:A107").Select
Range("A344").Select
Range("A8:O150").Select
Range("A150").Activate
ActiveWindow.SmallScroll Down:=-24
Selection.Copy
Windows("Inventory" & Masterfile & ".xlsm").Activate
ActiveWindow.SmallScroll Down:=-6
Range("A1:A100").Select
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection.Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 65535
      .TintAndShade = 0
      .PatternTintAndShade = 0
End With
Windows(sfile0 & ".xlsx").Activate
Windows(sfile0 & ".xlsx").Close False
End If
sfile = DateAdd("d", -1, sfile)
If Worksheets("Livestock").CheckBox2.Value = True Then
Workbooks.Open Filename:=sPath & sfile1 & ".xlsx"
Windows(sfile1 & ".xlsx").Activate 'choose Thursday file

Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A8").Select
ActiveCell.FormulaR1C1 = sfile 'add date
Range("A8").Select
Selection.AutoFill Destination:=Range("A8:A344"), Type:=xlFillCopy
Range("A8:A107").Select
Range("A344").Select
Range("A8:O150").Select
Range("A150").Activate
ActiveWindow.SmallScroll Down:=-24
Selection.Copy
Windows("Inventory" & Masterfile & ".xlsm").Activate
ActiveWindow.SmallScroll Down:=-6
Range("A101:A200").Select
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection.Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 65535
      .TintAndShade = 0
      .PatternTintAndShade = 0
End With
Windows(sfile1 & ".xlsx").Activate
Windows(sfile1 & ".xlsx").Close
End If

sfile = DateAdd("d", -1, sfile)
If Worksheets("Livestock").CheckBox3.Value = True Then
Workbooks.Open Filename:=sPath & sfile2 & ".xlsx"
Windows(sfile2 & ".xlsx").Activate 'choose Wednesday file

Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A8").Select
ActiveCell.FormulaR1C1 = sfile 'add date
Range("A8").Select
Selection.AutoFill Destination:=Range("A8:A344"), Type:=xlFillCopy
Range("A8:A107").Select
Range("A344").Select
Range("A8:O150").Select
Range("A150").Activate
ActiveWindow.SmallScroll Down:=-24
Selection.Copy
Windows("Inventory" & Masterfile & ".xlsm").Activate
ActiveWindow.SmallScroll Down:=-6
Range("A201:A300").Select
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection.Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 65535
      .TintAndShade = 0
      .PatternTintAndShade = 0
End With
Windows(sfile2 & ".xlsx").Activate
Windows(sfile2 & ".xlsx").Close
End If

sfile = DateAdd("d", -1, sfile)
If Worksheets("Livestock").CheckBox4.Value = True Then
Workbooks.Open Filename:=sPath & sfile3 & ".xlsx"
Windows(sfile3 & ".xlsx").Activate 'choose Tuesday file

Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A8").Select
ActiveCell.FormulaR1C1 = sfile 'add date
Range("A8").Select
Selection.AutoFill Destination:=Range("A8:A344"), Type:=xlFillCopy
Range("A8:A107").Select
Range("A344").Select
Range("A8:O150").Select
Range("A150").Activate
ActiveWindow.SmallScroll Down:=-24
Selection.Copy
Windows("Inventory" & Masterfile & ".xlsm").Activate
ActiveWindow.SmallScroll Down:=-6
Range("A301:A400").Select
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection.Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 65535
      .TintAndShade = 0
      .PatternTintAndShade = 0
End With
      Windows(sfile3 & ".xlsx").Activate
Windows(sfile3 & ".xlsx").Close
End If

sfile = DateAdd("d", -1, sfile)
If Worksheets("Livestock").CheckBox5.Value = True Then
Workbooks.Open Filename:=sPath & sfile4 & ".xlsx"
Windows(sfile4 & ".xlsx").Activate 'choose Monday file

Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A8").Select
ActiveCell.FormulaR1C1 = sfile 'add date
Range("A8").Select
Selection.AutoFill Destination:=Range("A8:A344"), Type:=xlFillCopy
Range("A8:A107").Select
Range("A344").Select
Range("A8:O150").Select
Range("A150").Activate
ActiveWindow.SmallScroll Down:=-24
Selection.Copy
Windows("Inventory" & Masterfile & ".xlsm").Activate
ActiveWindow.SmallScroll Down:=-6
Range("A401:A500").Select
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection.Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 65535
      .TintAndShade = 0
      .PatternTintAndShade = 0
End With
Windows(sfile4 & ".xlsx").Activate
Windows(sfile4 & ".xlsx").Close

End If

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

作者: cheercao 时间: 27-9-2015 16:39
thank you for sharing! it is so useful!

欢迎光临 FreeOZ论坛 (https://hioz.im/ibbs/)