如果匹配标准，则清除表的单元格值
如果匹配标准，则清除表的单元格值
提问于 2014-04-13 18:34:17
我希望对特定范围的单元格进行排序，并查看该单元格的LEN(gth)是否等于20。如果是的话，我想清除所述单元格的内容，并移到下一个单元格，直到它们都被评估为止。
我有这个报告，是从一个网站，并导出到excel和恼人是0.0%，显示为磅标志(# x的20)。我尝试过搜索字符串的其他宏，它完全忽略了0%，其中一个常量是，每个单元格的长度正好是20个字符。
应该发生的是我是：
1 Searching C3:U20
2 for cell.len = 20
3 if activecell = matches criteria
4 then clear.contents
5 Goto 1 until all activecells with a len of 20 are cleared谢谢你能提供的任何帮助。
G
*EDIT*，因为我不能做到这一点，所以我用了一个“工作”。这是低效的，因为所有的百叶窗，但因为它只使用在一个小的桌子，这并不是真正的问题。我只需要用“三个”独立的脚本来完成这个任务。我发现，如果我将范围从百分比格式转换为General，则只需将溢出号作为字符串查找即可。然后，一旦它们被“清除”，我就将这些列重新转换为百分比格式：
Sub sub_ConvertToGeneral()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''' Convert the Percentage Columns to General Format
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Columns("F:F").Select
'Application.FormulaBarHeight = 2
Range("F:F,H:H,L:M,O:O,S:S,U:U").Select
Range("U1").Activate
Selection.NumberFormat = "General"
'Selection.NumberFormat = "0.00%"
Range("A1:B1").Select
End Sub
Sub sub_Overflow()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''' Find Overflow and delete every instance of it
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim FindString As String
Dim rng As Range
FindString = "2.6965E+308"
50
If Trim(FindString) <> "" Then
With Sheets("Main Import").Range("C1:U20")
Set rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Application.Goto rng, True
ActiveCell.ClearContents
Do
Set rng = .FindNext(rng)
GoTo 50
Loop While Not rng Is Nothing
Else
End If
End With
End If
Range("A1").Select
End Sub
Sub sub_ConvertToPercentage()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''' Convert the General Columns to Percentage
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Columns("F:F").Select
'Application.FormulaBarHeight = 2
Range("F:F,H:H,L:L,O:O,S:S,U:U").Select
Range("U1").Activate
'Selection.NumberFormat = "General"
Selection.NumberFormat = "0.00%"
Range("A1:B1").Select
End Sub回答 1
Stack Overflow用户
回答已采纳
发布于 2014-04-13 19:44:11
如果要清除的单元格实际上包含确切的20个字符(因此LEN(A1)将显示20个字符)，则选择要检查的单元格并尝试：
Sub dural()
For Each r In Intersect(Selection, ActiveSheet.UsedRange)
If Len(r.Text) = 20 Then
r.Clear
End If
Next r
End SubEDIT#1:
This version will clear cells with a value greater than 1.0E+307:
Sub dural()
For Each r In Intersect(Selection, ActiveSheet.UsedRange)
If IsNumeric(r.Value) Then
If r.Value > 1E+307 Then
r.Clear
End If
End If
Next r
End Sub页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/23046715
复制相关文章
相似问题
腾讯云开发者
