excel - 如何在真正的空白单元格上进行查找和替换
问题描述
我有一个范围,它的大小可以变化,并且可以包含数万个单元格。对于此范围内包含字符串的每个单元格,我需要用 1 替换。对于根本没有值的每个单元格,我需要用零替换。
我尝试了以下方法,但是虽然它确实用单元格替换了填充的单元格,但空白单元格仍然是空白的。
Selection.Replace What:="*", Replacement:="1", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
我也试过这个,结果相同。
Selection.Replace What:=null, Replacement:="0", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
编辑:包括完整的代码
Sub MassFindReplace()
' This will select an area within the given parameters and replace all blank cells with zeros and all populated cells with Ones
Dim VRange1 As String
Dim VRange2 As String
Dim Doublecheck As Integer
VRange1 = InputBox("Enter First Cell Address Here" & vbNewLine & vbNewLine & "Make sure you ONLY input a single cell address")
VRange2 = InputBox("Enter Second Cell Address Here" & vbNewLine & vbNewLine & "Make sure you ONLY input a single cell address")
Range(VRange1, VRange2).Select
Doublecheck = MsgBox("The range you have selected is between " & VRange1 & " and " & VRange2 & vbNewLine & vbNewLine & "Does this sound right to you?" & vbNewLine & vbNewLine & "If not press No to cancel", vbYesNo)
If Doublecheck = vbYes Then
' This turns off a number of background functions and greatly speeds up this process
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
' choose what to search for and what to replace with here
Selection.Replace What:="*", Replacement:="1", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Selection.Cells.SpecialCells(xlCellTypeBlanks).Value = 1
'Resets the background functions. THIS MUST HAPPEN or it will screw up your excel.
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.CalculateFull
MsgBox "Complete"
Else
MsgBox "Canceled"
End If
End Sub
编辑:我尝试在下面的一些代码之后基于此,但虽然它似乎有效,但我无法让它选择自定义范围。
Sub MassTEST()
Dim ws As Worksheet: Set ws = ActiveSheet
Dim cel As Range
Dim VRange1 As String
Dim VRange2 As String
Dim Doublecheck As Integer
VRange1 = InputBox("Enter First Cell Address Here" & vbNewLine & vbNewLine & "Make sure you ONLY input a single cell address")
VRange2 = InputBox("Enter Second Cell Address Here" & vbNewLine & vbNewLine & "Make sure you ONLY input a single cell address")
Data = ws.Range(VRange1, VRange2).Value
For Each cel In ws.UsedRange
If cel.Value <> "" Then
cel.Value = 1
Else
cel.Value = 0
End If
Next
结束子
解决方案
如果您必须遍历并评估每个单元格,那么只需检查每个单元格以查看它是否为空。当然,如果工作表UsedRange
不是您需要的范围,您可以手动指定它。
Sub MassFindReplace()
Dim ws As Worksheet: Set ws = ActiveSheet
Dim cel As Range
For Each cel In ws.UsedRange
If cel.Value <> "" Then
cel.Value = 1
Else
cel.Value = 0
End If
Next
End Sub
根据 urdearboy 的建议,您也可以将其加载到数组中,然后在那里检查。
Sub MassFindReplace()
Dim ws As Worksheet: Set ws = ActiveSheet
Dim data As Variant, v As Variant
data = ws.UsedRange.Value
For i = LBound(data, 1) To UBound(data, 1)
For j = LBound(data, 2) To UBound(data, 2)
If data(i, j) <> "" Then
data(i, j) = 1
Else
data(i, j) = 0
End If
Next
Next
ws.UsedRange.Resize(UBound(data, 1), UBound(data, 2)).Value = data
End Sub
推荐阅读
- html - 在进度条背景上居中进度值
- java - 由于多个 java 错误,无法编译自动生成的 JOOQ 代码
- git - 忽略本地而不是远程的文件修改
- git - 如何修复长期运行的“git push”到 Heroku 远程响应“504 GATEWAY_TIMEOUT”?
- corda - 在 Corda 中,找不到方法 networkMap 异常
- angular - Angular 2:来自 API 的选项中的 p-dropdown 默认值选择
- javascript - 是的,条件验证
- android-studio - 54 错误:预期的类、接口或枚举错误:预期的类、接口或枚举
- android - MediaCodec 在 Android 4.1.2 上有一个关于 OMX.ST.VFM.H264Enc 的错误
- javascript - Angular4:处理浏览器刷新/关闭事件