这个宏应该在列“W”中找到值 NULL ,并在颜色中绘制它找到的NULL行。但是,如果我试图在同一列中搜索一个数字(我知道该列中有一个数字),它就不会找到值。任何帮助都将不胜感激。
Sub e()
MsgBox "some question?", , "Marvin The Paranoid Android"
Dim fNameAndPath As Variant, wb As Workbook
Dim LastRow As Long, i As Long, sht As Worksheet
Dim myValue As Variant
fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLSX), *.XLSX", Title:="Èçáåðåòå ôàéë ñ èìå /Ðåâîëâèíãè/")
If fNameAndPath = False Then Exit Sub
Set wb = Workbooks.Open(fNameAndPath)
Set sht = wb.Worksheets("Sheet1")
X = wb.Name
Windows(X).Activate
'Macro optimization
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
'000000000
ActiveWindow.Zoom = 85
Rows("1:1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A1:W1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("A:E,L:N").EntireColumn.AutoFit
Columns("F:F").ColumnWidth = 6.14
Columns("G:G").ColumnWidth = 6.43
Columns("H:K").ColumnWidth = 5.43
Range("O:R,T:V").ColumnWidth = 4.71
Columns("S:S").ColumnWidth = 14.71
Rows("1:1").RowHeight = 54.54
Range("A1").Select
myValue = InputBox("Give me some input")
LastRow = sht.Cells(sht.Rows.Count, "W").End(xlUp).row
For r = 1 To LastRow
If Cells(r, Columns("W").Column).Value = myValue Then
Rows(r).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
Next r
Range("A1").Select
Application.Calculation = xlCalculationAutomatic
wb.Close SaveChanges:=True 'or false
'Reverse macro optimization
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox "Îáðàáîòèõ ôàéë /Ðåâîëâèíãè/...", , "Marvin The Paranoid Android"
End Sub发布于 2016-08-10 14:05:02
Autofilter()方法的Range对象可以检测作为数字和文本的"23“:
With sht
With .Range("W1", .Cells(.Rows.Count, "W").End(xlUp)) '<--| consider column "W" values down to its last non empty row
.AutoFilter field:=1, Criteria1:=myValue '<--| filter column "W" on 'myValue'
If Application.WorksheetFunction.Subtotal(103, .Cells) > 1 Then '<--| if any values match...
With .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Interior '<--|... consider only filtered values, and apply formatting
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
End With
.ShowAllData '<--| show all rows back...
End With发布于 2016-08-10 13:46:05
当你说:-
如果我试图在同一列中搜索一个数字(我知道在该列中存在),它似乎找不到值
我假设您指的是代码中的下面一行,没有返回true并在If语句中运行代码。
If Cells(r, Columns("W").Column).Value = myValue Then总之,如果没有找到匹配,那么就没有匹配,但有时很难看到。
当你认为不匹配的例子是:-
12.12121212,但格式为12.12,则如果搜索12.12 (正如您认为匹配的那样),它将不匹配。'12.12 ' (末尾的空格),如果搜索12.12 (末尾没有空格),它将不匹配。我们可以从你的问题中看到你想要匹配的或者你认为应该匹配的内容,但是上面应该是从你的内容中找到答案所需要的信息。
根据注释,尝试用下面的代码修改代码,我添加了一些调试行,以帮助理解其失败的原因:-
'If the value is null it will skip trying to check it, this mean no type mismatch error
If Not IsNull(Cells(r, Columns("W").Column).Value) then
'This prints the value in the cell, the searched value, and if its seen as a match
Debug.Print "'" & Cells(r, Columns("W").Column).Value & "' ,'" & myValue & "', " & (Cells(r, Columns("W").Column).Value = myValue)
'Compares them both as Long data types
If CLng(Cells(r, Columns("W").Column).Value) = CLng(myValue) Then
'Your code
End If
End If发布于 2016-08-10 13:46:06
尝试用下面的代码替换您的For循环。如果您使用的是十进制值,或者大于整数的值,则从CInt更改为您的需要:
For r = 1 To LastRow
If sht.Cells(r, "W").Value = CInt(myValue) Then
sht.Rows(r).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
Next rhttps://stackoverflow.com/questions/38874829
复制相似问题