令人崩溃的EXCEL查找替换范围问题

Tags:excel, VBA, 替换.

一个工作表sheet1中A,B列都有值1，用下面语句将A列中的1该为2
sheet1.Range(”A1:A6″).Replace What:=”1″, Replacement:=”2″, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

问题是，如果excel“查找和替换”中的“替换范围”设定为“工作表”，可以正常替换，即只有A列中的1被改为2。

但是不小心将excel中crtl+h中“替换范围”设定为“整个工作簿”，那么A列和B列中的1都被改为2，而其他工作表中存在的1，也会被改为2。这意味着excel中查找和替换优先级高于宏的查找和替换。那么，如何在“替换范围”设定为“整个工作簿”的情况下，用replace命令只是替换指定范围内的值呢？或者如何先将替换范围改为“sheet”，然后再执行替换。

这个问题暂时无解，先记录下来。

EXCEL VBA笔记(不断更新中)

Tags:excel, VBA, VBE.

页面已转至：http://blog.jjuv.com/excel-vba

作为一个非IT专业人士，VBA的代码在不停的Google中忘记，为了方便自己查询，特作此笔记用以详细记录在代码中碰到的问题。
如果以下内容侵犯了您的版权，请来信告知，我会立刻删除。如无特殊说明，本笔记遵循《署名-非商业性使用-相同方式共享 2.5 中国大陆》方式进行共享
更新时间：Jul 02, 2009@10:30

Q1.Excel 中命名过的名称在vba中怎么使用
A1. 若某单元格或区域被命名为”aa”，则VBA中引用这个区域，range("aa")

Q2.VBA定义名称
A1.Sheets("工作表名称").range("单元格区域").Name="定义的名称"

Q3.如何在可以运行窗体的同时可以操作EXCEL工作簿（如何在工作表和窗体间切换）
A3.userform1.show vbmodeless
增加一个vbmodeless即可。
来源：http://club.excelhome.net/thread-348925-1-1.html
作者：tonyibm

Q4.获取人名拼音首字母的excel函数
A4.http://blog.jjuv.com/2009/04/19/first-letter-of-the-chinese-names-excel-function.html

Q5.Excel工作簿中工作表分别保存为文件
A5.http://blog.jjuv.com/2009/03/13/excel-worksheet-saved-as-files-separately.html

Q6.如何将excel工作薄中的单列数据复制到不同的新工作簿中
A6.http://blog.jjuv.com/2009/03/10/how-to-excel-in-a-separate-job-thin-data-replication-to-a-different-new-workbook.html

Q7.VBA中获取文件名的方法
A7.http://blog.jjuv.com/2008/03/02/vba-in-the-way-of-access-to-documents.html

Q8.选择性粘贴数值
A8.
ar=ActiveWorkbook.Sheets(1).Columns("L:L")
ThisWorkbook.Sheets("汇总").Columns("E:E")=ar

Q9.如何修改单元格触发宏运行；
A9.例子如下：
Private Sub Worksheet_Change(ByVal Target As Range)
Dim mo As String
If Target.Count > 1 Then Exit Sub
If Target.Address <> "$D$3" Then Exit Sub '(此处地址需用大写)
mo = Target.Value
If mo = 6 Or mo = 12 Then
MsgBox mo & "月需要填写相关资料"
Rows("29:56").Hidden = False
Else
Rows("29:56").Hidden = True
End If
End Sub

Q10.如何取消筛选
A10.AutoFilterMode = False

Q11.判断EXCEL表格中已使用的行数
A11.Sheets("sheet1").UsedRange.Rows.Count

Q12.向单元格中写入公式
A12.普通公式：range("a1")="=sum(a2:a4")"
数组公式：Range("g18").FormulaArray = "=SUM((fa_cat='28-AC1-5'!A18)*(RIGHT(fa_change,2)=""折旧"")*(fa_depr))"

Excel工作簿中工作表分别保存为文件

Tags:excel, VBA, 保存.


Sub fenchai()
On Error Resume Next
Dim WB As Workbook, i As Integer
Set WB = ThisWorkbook
For i = 1 To WB.Worksheets.Count '获得工作簿中表的个数
WB.Worksheets(i).Copy '复制工作表
ActiveWorkbook.SaveAs Filename:="c:\分拆文档\" & WB.Worksheets(i).Name & "TB.xls" '保存文档
ActiveWindow.Close
Next i
End Sub

如何将excel工作薄中的单列数据复制到不同的新工作簿中

Tags:excel, VBA, 汇总.

汇总报表中单列公司的数据要分发到下属公司，如果每一列都复制到新工作簿另存，工作很大，所以编写了一段VBA来代替重复的手工劳动
Sub add_new()
On Error Resume Next
With Worksheets("Detail line-consolidation") '汇总报表
For i = 3 To 35
Dim fname As String
fname = .Cells(3, i) '取公司名称
Dim mybook As Workbook
Set mybook = Workbooks.Add
mybook.SaveAs "d:\vba\" & fname & ".xls"
col = Split(Cells(3, i).Address, "$")(1) '取列名
.Range("a2:b169").Copy
Windows(fname & ".xls").Activate
Range("a1").PasteSpecial
.Range(col & "2:" & col & "169").Copy
Windows(fname & ".xls").Activate
Range("c1").PasteSpecial
Workbooks(fname & ".xls").Close savechanges:=True
Set mybook = Nothing
Next i
End With
End Sub

VBA中获取文件名的方法

Tags:excel, VBA, 文件名.

在用excel做文件导入时发现，一旦使用Commond Dialogs 就会出错

“The control could not be created because it is not properly licensed”

原因是，Commond Dialogs are not licensed to be used in Excel。经过搜索发现，其实VBA还有其它的法子获得文件路径。

1
2
3
4
5
6
7
8
9
10

Private Function OpenDialog() As String
     Dim sFile As String
     Dim n As Integer
     '获取路径
     sFile = Application.GetOpenFilename("MS excel (*.xls), *.xls")  
     '获取文件title的长度
     n = Len(sFile) - InStrRev(sFile, "\") 
     '获得文件title
     tt = Right(sFile, n) 
End Function