首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >Excel VBA -循环搜索值,然后搜索颜色行

问Excel VBA -循环搜索值,然后搜索颜色行
EN

Stack Overflow用户
提问于 2016-08-10 13:27:59
回答 4查看 1.3K关注 0票数 0

这个宏应该在列“W”中找到值 NULL ,并在颜色中绘制它找到的NULL行。但是,如果我试图在同一列中搜索一个数字(我知道该列中有一个数字),它就不会找到值。任何帮助都将不胜感激。

代码语言:javascript
复制
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
EN

回答 4

Stack Overflow用户

回答已采纳

发布于 2016-08-10 14:05:02

Autofilter()方法的Range对象可以检测作为数字和文本的"23“:

代码语言:javascript
复制
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
票数 0
EN

Stack Overflow用户

发布于 2016-08-10 13:46:05

当你说:-

如果我试图在同一列中搜索一个数字(我知道在该列中存在),它似乎找不到值

我假设您指的是代码中的下面一行,没有返回true并在If语句中运行代码。

代码语言:javascript
复制
If Cells(r, Columns("W").Column).Value = myValue Then

总之,如果没有找到匹配,那么就没有匹配,但有时很难看到。

当你认为不匹配的例子是:-

  • 如果单元格包含12.12121212,但格式为12.12,则如果搜索12.12 (正如您认为匹配的那样),它将不匹配。
  • 如果单元格包含前导或尾随空格,则'12.12 ' (末尾的空格),如果搜索12.12 (末尾没有空格),它将不匹配。

我们可以从你的问题中看到你想要匹配的或者你认为应该匹配的内容,但是上面应该是从你的内容中找到答案所需要的信息。

根据注释,尝试用下面的代码修改代码,我添加了一些调试行,以帮助理解其失败的原因:-

代码语言:javascript
复制
'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
票数 0
EN

Stack Overflow用户

发布于 2016-08-10 13:46:06

尝试用下面的代码替换您的For循环。如果您使用的是十进制值,或者大于整数的值,则从CInt更改为您的需要:

代码语言:javascript
复制
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 r
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/38874829

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档