[已解决]遍历工作簿工作表单元格

乐乐2006201506 · 发表于 2016-5-18 14:58

本帖最后由乐乐2006201506 于 2016-5-18 18:18 编辑

   下面代码只能实现活动工作表中有数据单元格的公式清除和14位以上数字的完整显示，但如果一个工作簿中有两个以上工作表时，非活动工作表中的数据不起作用。希望将ActiveSheet改为所有工作表，可以重新写可以实现下面效果的代码，谢谢！
Sub setText(sh As Worksheet)
Dim cell As Range '声明单元格对象变量
Dim 计算状态 As Long
计算状态 = Application.Calculation
If 计算状态 = xlAutomatic Then Application.Calculation = xlManual '如当前是自动改为手动计算,不然如有随机函数会在转化过程中数值变化
For Each cell In ActiveSheet.UsedRange
      If Len(cell) > 14 Then
         cell.NumberFormat = "@"
         cell.Value = cell.Value
      ElseIf cell.HasFormula Then
         cell.NumberFormat = "@"
         cell.Value = cell.Value
      End If
Next cell
Application.Calculation = 计算状态  '恢复之前计算状态
End Sub

最佳答案

月排行榜 / 总排行榜

七彩屋

2016-5-18 15:16

Sub setText()
Dim sht As Worksheet
Dim cell As Range '声明单元格对象变量
Dim 计算状态 As Long
计算状态 = Application.Calculation
If 计算状态 = xlAutomatic Then Application.Calculation = xlManual '如当前是自动改为手动计算,不然如有随机函数会在转化过程中数值变化
For Each sht In Worksheets
For Each cell In sht.UsedRange
If Len(cell) > 14 Then
cell.NumberFormat = "@"
cell.Value = cell.Value
ElseIf cell.HasFormula Then
cell.NumberFormat = "@"
cell.Value = cell.Value
End If
Next cell
Next sht
Application.Calculation = 计算状态 '恢复之前计算状态
End Sub

复制代码

跳转到最佳答案楼层

七彩屋 · 发表于 2016-5-18 15:16

Sub setText()
Dim sht As Worksheet
Dim cell As Range '声明单元格对象变量
Dim 计算状态 As Long
计算状态 = Application.Calculation
If 计算状态 = xlAutomatic Then Application.Calculation = xlManual '如当前是自动改为手动计算,不然如有随机函数会在转化过程中数值变化
For Each sht In Worksheets
For Each cell In sht.UsedRange
If Len(cell) > 14 Then
cell.NumberFormat = "@"
cell.Value = cell.Value
ElseIf cell.HasFormula Then
cell.NumberFormat = "@"
cell.Value = cell.Value
End If
Next cell
Next sht
Application.Calculation = 计算状态 '恢复之前计算状态
End Sub

复制代码

		自动登录	找回密码
密码			注册