在 Excel 中使用正则表达式，我可以在替换匹配的字符串之前对匹配的模式执行一些算术吗？

发布于 2024-12-09 02:15:10 字数 442 浏览 0 评论 0原文

我正在使用“VBscript.RegExp”来查找和替换正则表达式。我正在尝试做这样的事情：

Dim regEx
Set regEx = CreateObject("VBScript.RegExp")
regEx.Pattern = "ID_(\d{3})"
regEx.IgnoreCase = False
regEx.Global = True
regEx.Replace(a_cell.Value, "=HYPERLINK(A" & CStr(CInt("$1") + 2) )

即我有一些包含 ID_006 之类的单元格，我想用单元格 A8 的超链接替换此类单元格的内容。因此，我匹配这三个数字，然后想在这些数字上添加 2，以获得要超链接的正确行。

但 CStr(CInt("$1") + 2) 部分不起作用。关于如何让它发挥作用有什么建议吗？

原文

I am using `VBscript.RegExp`` to find and replace using a regular expression. I'm trying to do something like this:

Dim regEx
Set regEx = CreateObject("VBScript.RegExp")
regEx.Pattern = "ID_(\d{3})"
regEx.IgnoreCase = False
regEx.Global = True
regEx.Replace(a_cell.Value, "=HYPERLINK(A" & CStr(CInt("$1") + 2) )

I.e. I have cells which contain things like ID_006 and I want to replace the contents of such a cell with a hyperlink to cell A8. So I match the three digits, and then want to add 2 to those digits to get the correct row to hyperlink to.

But the CStr(CInt("$1") + 2) part doesn't work. Any suggestions on how I can make it work?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

可遇━不可求 2024-12-16 02:15:10

我已经发布了这些要点，

您应该在尝试从当前代码进行替换之前测试有效匹配
，全局是多余的，因为您可以向单元格添加 1 个超链接（1 个匹配）
，如果您愿意，您当前的代码将接受部分字符串匹配为了避免 ID_9999，请使用 ^ 和 $ 匹配整个字符串。此版本运行我，您可以使用 .Pattern = "ID_(\d{3})" 恢复到当前模式
。通常，添加超链接时需要可见地址。下面的代码执行此操作（一次完成行操作）

下面的代码在 A1:A10 运行（示例显示转储到 B1:B10 以进行前和后 coede）

 Sub ParseIt()
    Dim rng1 As Range
    Dim rng2 As Range
    Dim regEx
    Set rng1 = Range([a1], [a10])
    Set regEx = CreateObject("VBScript.RegExp")
    With regEx
        'match entire string
        .Pattern = "^ID_(\d{3})$"
        'match anywhere
       ' .Pattern = "ID_(\d{3})"
        .IgnoreCase = False
        For Each rng2 In rng1
            If .test(rng2.Value) Then
            'use Anchor:=rng2.Offset(0, 1) to dump one column to the right)
                ActiveSheet.Hyperlinks.Add Anchor:=rng2, Address:="", SubAddress:= _
                                           Cells(.Replace(rng2.Value, "$1") + 2, rng2.Column).Address, TextToDisplay:=Cells(.Replace(rng2.Value, "$1") + 2, rng2.Column).Address
            End If
        Next
    End With
End Sub

在此处输入图像描述

Ive posted given these points

you should test for a valid match before trying a replace
from your current code the Global is redundant as you can add 1 hyerplink (1 match) to a cell
your current code will accept a partial string match, if you wanted to avoid ID_9999 then you match the entire string using ^ and $. This version runs me, you can revert to your current pattern with .Pattern = "ID_(\d{3})"
Normally when adding a hyperlink a visible address is needed. The code beloe does this (with the row manipulation in one shot)

The code below runs at A1:A10 (sample shown dumping to B1:B10 for pre and post coede)

 Sub ParseIt()
    Dim rng1 As Range
    Dim rng2 As Range
    Dim regEx
    Set rng1 = Range([a1], [a10])
    Set regEx = CreateObject("VBScript.RegExp")
    With regEx
        'match entire string
        .Pattern = "^ID_(\d{3})$"
        'match anywhere
       ' .Pattern = "ID_(\d{3})"
        .IgnoreCase = False
        For Each rng2 In rng1
            If .test(rng2.Value) Then
            'use Anchor:=rng2.Offset(0, 1) to dump one column to the right)
                ActiveSheet.Hyperlinks.Add Anchor:=rng2, Address:="", SubAddress:= _
                                           Cells(.Replace(rng2.Value, "$1") + 2, rng2.Column).Address, TextToDisplay:=Cells(.Replace(rng2.Value, "$1") + 2, rng2.Column).Address
            End If
        Next
    End With
End Sub

enter image description here

回复收藏 0 原文

︶葆Ⅱㄣ 2024-12-16 02:15:10

这是因为： "=HYPERLINK(A" & CStr(CInt("$1") + 2) 在执行代码时计算一次，而不是每次匹配都计算一次。

您需要捕获& 像这样处理匹配；

a_cell_Value = "*ID_006*"
Set matches = regEx.Execute(a_cell_Value)
Debug.Print "=HYPERLINK(A" & CLng(matches(0).SubMatches(0)) + 2 & ")"

>> =HYPERLINK(A8)

或者如果它们都是 ??_NUM 格式；

a_cell_Value = "ID_11"
?"=HYPERLINK(A" & (2 + val(mid$(a_cell_Value, instr(a_cell_Value,"_") +1))) & ")"
=HYPERLINK(A13)

This is because: "=HYPERLINK(A" & CStr(CInt("$1") + 2) is evaluated once, when the code is executed, not once for every match.

You need to capture & process the match like this;

a_cell_Value = "*ID_006*"
Set matches = regEx.Execute(a_cell_Value)
Debug.Print "=HYPERLINK(A" & CLng(matches(0).SubMatches(0)) + 2 & ")"

>> =HYPERLINK(A8)

Or if they are all in ??_NUM format;

a_cell_Value = "ID_11"
?"=HYPERLINK(A" & (2 + val(mid$(a_cell_Value, instr(a_cell_Value,"_") +1))) & ")"
=HYPERLINK(A13)

回复收藏 0 原文

迷迭香的记忆 2024-12-16 02:15:10

该行 -

regEx.Replace(a_cell.Value, "=HYPERLINK(A" & CStr(CInt("$1") + 2) )

不起作用，因为 VBA 会尝试对文字字符串“$1”而不是对正则表达式的匹配项执行 CInt。

如果您分两步进行替换，就会起作用，如下所示 -

Dim a_cell
a_cell = Sheets(1).Cells(1, 1)
Dim regEx
Set regEx = CreateObject("VBScript.RegExp")
regEx.Pattern = "ID_(\d{3})"
regEx.IgnoreCase = False
regEx.Global = True
a_cell = regEx.Replace(a_cell, "$1")
Sheets(1).Cells(1, 1) = "=HYPERLINK(A" & CStr(CInt(a_cell) + 2) & ")"

The line -

regEx.Replace(a_cell.Value, "=HYPERLINK(A" & CStr(CInt("$1") + 2) )

won't work as VBA will try to do a CInt on the literal string "$1" rather than on the match from your RegEx.

It would work if you did your replace in 2 steps, something like this -

Dim a_cell
a_cell = Sheets(1).Cells(1, 1)
Dim regEx
Set regEx = CreateObject("VBScript.RegExp")
regEx.Pattern = "ID_(\d{3})"
regEx.IgnoreCase = False
regEx.Global = True
a_cell = regEx.Replace(a_cell, "$1")
Sheets(1).Cells(1, 1) = "=HYPERLINK(A" & CStr(CInt(a_cell) + 2) & ")"

回复收藏 0 原文

~没有更多了~