如何使用& quot; #VALUE！& quot;查找单元格在EXCEL 2010 VBA中 [英] how to find cells with "#VALUE!" in EXCEL 2010 VBA

问题描述

我需要通过EXCEL 2012 VBA在列中找到最大值.一些单元格具有"#VALUE！".我的代码不起作用.

I need to find the max value in a column by EXCEL 2012 VBA. Some cells have "#VALUE!". My code does not work.

Sub find_max()
   Dim rng As Range
   Dim dblMax As Double
  dblMax = 0
  Set rng = Range("A2:A11")
  For Each cell In rng
     If (cell.Value <> "#VALUE!") Then    // error ! type mismatch
         If dblMax < CDbl(cell.Value) Then
             dblMax = CDbl(cell.Value)
         End If
    End If
 Next cell
 MsgBox dblMax
End Sub

我还需要打印带有最大值的单元格位置(用颜色标记).

I also need to print the cell location (mark it with a color) with the max value.

任何帮助将不胜感激.

推荐答案

如果您的范围具有常量，请使用

If your range has constants, use

application.WorksheetFunction.Max(range("B6:C13").SpecialCells(xlcelltypeconstants,xlNumbers ))

如果有公式，请使用

application.WorksheetFunction.Max(range("B6:C13").SpecialCells(xlcelltypeformulas,xlNumbers ))

对于找到的范围， .Find 应该可以正常工作:

For the range where it's found, .Find should work fine:

Sub find_max()


 Dim rng As Range
   Dim dblMax As Double, rgMax As Range

    Set rng = Range("A2:A11")

    dblMax = Application.WorksheetFunction.Max(rng.SpecialCells(xlCellTypeFormulas, xlNumbers))

    Set rgMax = rng.Find(dblMax, , xlValues, xlWhole)

 MsgBox rgMax.Address & ": " & dblMax

End Sub

这篇关于如何使用& quot; #VALUE！& quot;查找单元格在EXCEL 2010 VBA中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！