如何从单元格中提取范围（单元格包含范围值）

I am basically using a script that somebody else designed which draws arrows on the excel sheet between various points. I have data in my spreadsheet which shows the cells that need linking and I am trying to call the value of this cells into the call for the macro script that I am using and running it as a loop so that it creates all the arrows I want

我基本上使用的是其他人设计的脚本,它在各个点之间的excel表上绘制箭头。我的电子表格中有数据显示需要链接的单元格,我试图将此单元格的值调用到我正在使用的宏脚本的调用中,并将其作为循环运行,以便创建我想要的所有箭头

It fails every time on the lines where I am adding .value. I have tried different ways following similar issues but none doing exactly what i am asking so i am no at a loss

它每次都在我添加.value的行上失败。我已经尝试过类似问题的不同方法,但没有做任何我正在问的问题,所以我不会感到茫然

the stored range value that i am trying to extract looks like this $B$5 .

我想要提取的存储范围值看起来像这个$ B $ 5。

Private Sub CommandButton1_Click()
Dim rng As Range, cell As Range, rngstart As Range, rngend As Range

Set rng = Range("E5:E100")
    For Each cell In rng
        If cell.Value = "Yes" Then
            cell.Select
            Set rngstart = Range("K" & (ActiveCell.Row)).Value
            Set rngend = Range("H" & (ActiveCell.Row)).Value
            Call DrawArrows(Range(rngstart), Range(rngend), RGB(0, 0, 255), "Single")
        End If
    Next cell
End Sub

the original code before for calling the script looked like this

调用脚本之前的原始代码看起来像这样

Call DrawArrows(Range("b1"), Range("c4"), RGB(0, 0, 255), "Double")

2 个解决方案

#1

You are using rngstart and rngend as if they were strings in your call to DrawArrows...

您正在使用rngstart和rngend,就好像它们是您对DrawArrows的调用中的字符串一样...

        Call DrawArrows(Range(rngstart), Range(rngend), RGB(0, 0, 255), "Single")

...but you're treating them as objects here:

...但你在这里将它们视为对象:

        Set rngstart = Range("K" & (ActiveCell.Row)).Value
        Set rngend = Range("H" & (ActiveCell.Row)).Value

This should be a run-time error 424, because you're retrieving a Variant (which based on your question is a String sub-type) and attempting to assign it to a variable declared as a Range.

这应该是运行时错误424,因为您正在检索Variant(基于您的问题是String子类型)并尝试将其分配给声明为Range的变量。

Private Sub CommandButton1_Click()
    Dim rng As Range, cell As Range

    With Sheet1 '<-- you should be explicitly referencing a worksheet. replace as required.
        Set rng = .Range("E5:E100")
        For Each cell In rng
            If cell.Value = "Yes" Then
                Dim rngstart As String, rngend As String
                rngstart = .Cells(cell.Row, 11).Text
                rngend = .Cells(cell.Row, 8).Text
                'ditto with the explicit reference.
                Call DrawArrows(.Range(rngstart), .Range(rngend), RGB(0, 0, 255), "Single")
            End If
        Next cell
    End With
End Sub

#2

The line Set rngstart = Range("K" & (ActiveCell.Row)).Value is assigning the .Value stored in column K, row ActiveCell.Row to the Range variable rngstart. You want to assign the range to pass in.

行设置rngstart = Range(“K”&(ActiveCell.Row))。值将存储在列K中的.Value,行ActiveCell.Row分配给Range变量rngstart。您想要指定要传入的范围。

Doing some quick "air-coding", something like this should work:

做一些快速的“空气编码”,这样的事情应该有效:

Set rngstart = ActiveSheet.Range("K" & (ActiveCell.Row))

Note: I've specified ActiveSheet, because that's what the otherwise unquilified Range points to - at least this way it's explicit. Also, this could easily break if someone were to click on another sheet, or the button is on a different sheet than the one where you're looking to pick up the cell address from.

注意:我已经指定了ActiveSheet,因为这是其他无法区分的Range指向的内容 - 至少这是明确的。此外,如果有人要点击其他工作表,或者按钮位于与您要从中获取单元格地址的工作表不同的工作表上,则可能很容易中断。

#1