设为首页收藏本站
简字体切换切换到宽版

ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步，快速开始

快捷登录

搜索
ExcelHome技术论坛»论坛 › Excel 技术交流与问答 › Excel VBA程序开发 › 求助各位老师，录制宏批量替换1900-1-0但是无法执行
EH技术汇-专业的职场技能充电站 妙哉！函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 效率神器，一键搞定繁琐工作
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
让更多数据处理，一键完成 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
返回列表 发新帖
查看: 1329|回复: 2

[求助] 求助各位老师，录制宏批量替换1900-1-0但是无法执行

[复制链接]
wangshi467

TA的精华主题

TA的得分主题
发表于 2018-9-26 11:28 | 显示全部楼层 |阅读模式
[广告] ★Excel Home云课堂 - 专业的职场技能充电站★         ★Excel三大利器★        ★ 笨办法学VBA全系列★
求助各位老师，录制宏批量替换1900-1-0为空白但是无法执行，但批量替换#N/A确会成功，录制完宏后公式如下
Sub 获取数据()
'
' 获取数据 宏
'

'
    Range("L4").Select
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],数据!C[-11]:C[1],13,0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-8],数据!C[-12]:C,4,0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-9],数据!C[-13]:C[-1],5,0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-10],数据!C[-14]:C[-2],6,0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-11],数据!C[-15]:C[-3],7,0)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-12],数据!C[-16]:C[-4],9,0)"
    ActiveCell.Offset(1, 0).Range("A1").Select
End Sub
Sub 覆盖数据()
'
' 覆盖数据 宏
'

'
    ActiveCell.Range("A1:F1").Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:F304"), Type:= _
        xlFillDefault
    ActiveCell.Range("A1:F304").Select
End Sub
Sub 去错()
'
' 去错 宏
'

'
    Range("L4:Q307").Select
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-138
    ActiveWindow.ScrollRow = 154
    ActiveWindow.ScrollRow = 153
    ActiveWindow.ScrollRow = 152
    ActiveWindow.ScrollRow = 150
    ActiveWindow.ScrollRow = 149
    ActiveWindow.ScrollRow = 145
    ActiveWindow.ScrollRow = 143
    ActiveWindow.ScrollRow = 141
    ActiveWindow.ScrollRow = 139
    ActiveWindow.ScrollRow = 137
    ActiveWindow.ScrollRow = 135
    ActiveWindow.ScrollRow = 132
    ActiveWindow.ScrollRow = 129
    ActiveWindow.ScrollRow = 122
    ActiveWindow.ScrollRow = 119
    ActiveWindow.ScrollRow = 116
    ActiveWindow.ScrollRow = 113
    ActiveWindow.ScrollRow = 107
    ActiveWindow.ScrollRow = 104
    ActiveWindow.ScrollRow = 101
    ActiveWindow.ScrollRow = 98
    ActiveWindow.ScrollRow = 92
    ActiveWindow.ScrollRow = 85
    ActiveWindow.ScrollRow = 82
    ActiveWindow.ScrollRow = 78
    ActiveWindow.ScrollRow = 75
    ActiveWindow.ScrollRow = 69
    ActiveWindow.ScrollRow = 66
    ActiveWindow.ScrollRow = 60
    ActiveWindow.ScrollRow = 57
    ActiveWindow.ScrollRow = 54
    ActiveWindow.ScrollRow = 51
    ActiveWindow.ScrollRow = 44
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 39
    ActiveWindow.ScrollRow = 37
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Range("L4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("L4:Q307").Select
    ActiveWindow.SmallScroll Down:=-39
    Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ActiveWindow.ScrollRow = 253
    ActiveWindow.ScrollRow = 252
    ActiveWindow.ScrollRow = 251
    ActiveWindow.ScrollRow = 250
    ActiveWindow.ScrollRow = 249
    ActiveWindow.ScrollRow = 247
    ActiveWindow.ScrollRow = 244
    ActiveWindow.ScrollRow = 239
    ActiveWindow.ScrollRow = 237
    ActiveWindow.ScrollRow = 234
    ActiveWindow.ScrollRow = 228
    ActiveWindow.ScrollRow = 224
    ActiveWindow.ScrollRow = 220
    ActiveWindow.ScrollRow = 212
    ActiveWindow.ScrollRow = 208
    ActiveWindow.ScrollRow = 203
    ActiveWindow.ScrollRow = 199
    ActiveWindow.ScrollRow = 195
    ActiveWindow.ScrollRow = 191
    ActiveWindow.ScrollRow = 189
    ActiveWindow.ScrollRow = 184
    ActiveWindow.ScrollRow = 181
    ActiveWindow.ScrollRow = 176
    ActiveWindow.ScrollRow = 173
    ActiveWindow.ScrollRow = 164
    ActiveWindow.ScrollRow = 159
    ActiveWindow.ScrollRow = 154
    ActiveWindow.ScrollRow = 150
    ActiveWindow.ScrollRow = 148
    ActiveWindow.ScrollRow = 140
    ActiveWindow.ScrollRow = 137
    ActiveWindow.ScrollRow = 130
    ActiveWindow.ScrollRow = 127
    ActiveWindow.ScrollRow = 124
    ActiveWindow.ScrollRow = 117
    ActiveWindow.ScrollRow = 114
    ActiveWindow.ScrollRow = 111
    ActiveWindow.ScrollRow = 105
    ActiveWindow.ScrollRow = 102
    ActiveWindow.ScrollRow = 100
    ActiveWindow.ScrollRow = 97
    ActiveWindow.ScrollRow = 93
    ActiveWindow.ScrollRow = 90
    ActiveWindow.ScrollRow = 88
    ActiveWindow.ScrollRow = 84
    ActiveWindow.ScrollRow = 81
    ActiveWindow.ScrollRow = 79
    ActiveWindow.ScrollRow = 75
    ActiveWindow.ScrollRow = 73
    ActiveWindow.ScrollRow = 71
    ActiveWindow.ScrollRow = 69
    ActiveWindow.ScrollRow = 64
    ActiveWindow.ScrollRow = 61
    ActiveWindow.ScrollRow = 59
    ActiveWindow.ScrollRow = 57
    ActiveWindow.ScrollRow = 56
    ActiveWindow.ScrollRow = 54
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Range("L4:Q307").Select
    Selection.Replace What:="1900-1-0", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ActiveWindow.ScrollRow = 292
    ActiveWindow.ScrollRow = 291
    ActiveWindow.ScrollRow = 290
    ActiveWindow.ScrollRow = 289
    ActiveWindow.ScrollRow = 288
    ActiveWindow.ScrollRow = 287
    ActiveWindow.ScrollRow = 286
    ActiveWindow.ScrollRow = 284
    ActiveWindow.ScrollRow = 283
    ActiveWindow.ScrollRow = 282
    ActiveWindow.ScrollRow = 281
    ActiveWindow.ScrollRow = 279
    ActiveWindow.ScrollRow = 278
    ActiveWindow.ScrollRow = 276
    ActiveWindow.ScrollRow = 272
    ActiveWindow.ScrollRow = 266
    ActiveWindow.ScrollRow = 263
    ActiveWindow.ScrollRow = 258
    ActiveWindow.ScrollRow = 255
    ActiveWindow.ScrollRow = 253
    ActiveWindow.ScrollRow = 247
    ActiveWindow.ScrollRow = 245
    ActiveWindow.ScrollRow = 242
    ActiveWindow.ScrollRow = 235
    ActiveWindow.ScrollRow = 228
    ActiveWindow.ScrollRow = 218
    ActiveWindow.ScrollRow = 209
    ActiveWindow.ScrollRow = 205
    ActiveWindow.ScrollRow = 200
    ActiveWindow.ScrollRow = 196
    ActiveWindow.ScrollRow = 189
    ActiveWindow.ScrollRow = 185
    ActiveWindow.ScrollRow = 175
    ActiveWindow.ScrollRow = 161
    ActiveWindow.ScrollRow = 156
    ActiveWindow.ScrollRow = 150
    ActiveWindow.ScrollRow = 143
    ActiveWindow.ScrollRow = 136
    ActiveWindow.ScrollRow = 132
    ActiveWindow.ScrollRow = 125
    ActiveWindow.ScrollRow = 117
    ActiveWindow.ScrollRow = 114
    ActiveWindow.ScrollRow = 105
    ActiveWindow.ScrollRow = 99
    ActiveWindow.ScrollRow = 94
    ActiveWindow.ScrollRow = 90
    ActiveWindow.ScrollRow = 84
    ActiveWindow.ScrollRow = 79
    ActiveWindow.ScrollRow = 74
    ActiveWindow.ScrollRow = 71
    ActiveWindow.ScrollRow = 69
    ActiveWindow.ScrollRow = 66
    ActiveWindow.ScrollRow = 63
    ActiveWindow.ScrollRow = 61
    ActiveWindow.ScrollRow = 58
    ActiveWindow.ScrollRow = 56
    ActiveWindow.ScrollRow = 52
    ActiveWindow.ScrollRow = 49
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 44
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 39
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Range("L4").Select
End Sub


求助, 各位, 老师, 录制, 批量
回复

举报

gdw831001

TA的精华主题

TA的得分主题
发表于 2018-9-26 12:28 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助★
上附件                       
回复

举报

vbload999

TA的精华主题

TA的得分主题
发表于 2018-9-26 12:32 | 显示全部楼层
[广告] ★用Excel做企业管理系统，Excel服务器学习和下载★
Replacement:=Null
试试，我也只是猜的，没实践
回复

举报

返回列表 发新帖
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2025-1-16 03:54 , Processed in 0.019113 second(s), 9 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见，任何违反国家相关法律的言论，本站将协助国家相关部门追究发言者责任！     本站特聘法律顾问：李志群律师

快速回复 返回顶部 返回列表