excel - 单元格的位置并提取数值

我目前正在“尝试”在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

最佳答案

我发现这是一个很好的问题，因此在答案中做了一些工作。我认为这将满足您的要求!它甚至适用于十进制和千位分隔符。

我确实承认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