excel - 单元格位置和提取数值

Question

我目前正在“尝试”在 Excel 中设置一个网格

• 用户输入引用(例如 HP1 或 HP234)，
• 我可以自动检测输入的单元格和单元格中的数值(例如 HP1 = 1，HP234 = 234)。

我已经开始玩下面的代码了。在 msgbox("work") 部分 - 我只是用来测试它周围的代码。在这里，我想返回单元格中的数值和单元格位置，以便我可以将它们放入报告中。

如有任何帮助，我们将不胜感激。

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Dim rngTarget As Range

Set rngTarget = Range("a1:a100")    
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then        
    For Each rng In rngTarget   
        If InStr(1, prNg, "H") > 0 And InStr(1, rngEachValue, "P") = 0 Then
               MsgBox ("works")            
        End If    
    Next    
End If    

End Sub

Answer 1

我发现这是一个很好的问题，所以要花一些功夫来回答。我认为这会做你想要的!它甚至可以使用小数点和千位分隔符。

我承认 NumericalValue 函数也可以用不同的方式创建(找到第一个和最后一个数字，然后取字符串的 mid 部分。

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim rngTarget As Range
    Dim varValue As Variant

    Set rngTarget = Range("a1:a100")

    If Not Intersect(Target, rngTarget) Is Nothing Then
        For Each rng In rngTarget
            'only check cells that contain an H and a P
            If InStr(1, rng, "H") > 0 And InStr(1, rng, "P") > 0 Then
                'find the numerical value if any (Empty if not found)
                varValue = NumericalValue(rng.Value2)
                If Not IsEmpty(varValue) Then
                    MsgBox "hurray the value of cell " & rng.AddressLocal & " is " & varValue
                End If
            End If
        Next
    End If

End Sub

'return the first numerical value found in the cell
Private Function NumericalValue(ByVal strValue As String) As Variant
    Dim intChar As Integer
    Dim booNumberFound As Boolean
    Dim intDecimal As Integer

    booNumberFound = False

    NumericalValue = Val(strValue)

    For intChar = 1 To Len(strValue) Step 1
        'if a number found then grow the total numerical value with it
        If IsNumeric(Mid(strValue, intChar, 1)) Then
            NumericalValue = NumericalValue * IIf(intDecimal = 0, 10, 1) + _
                    Val(Mid(strValue, intChar, 1)) * 10 ^ -intDecimal
            If intDecimal > 0 Then
                intDecimal = intDecimal + 1
            End If
            booNumberFound = True
        'if the decimal separator is found then set the decimal switch
        ElseIf intDecimal = 0 And booNumberFound = True And Mid(strValue, intChar, 1) = Application.DecimalSeparator Then
            intDecimal = 1
        'skip the thousand separator to find more numbers
        ElseIf booNumberFound = True And Mid(strValue, intChar, 1) = Application.ThousandsSeparator Then
        ElseIf booNumberFound = True Then
            Exit For
        End If
    Next intChar

End Function