CODEWITHSUNDEEP
excelvbacell
UnknownError
UnknownError

Reputation: 103

How do extract a range from a cell (where the cell contains the range value)

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

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

the stored range value that i am trying to extract looks like this $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")

Upvotes: 0

Views: 268

Answers (2)

FreeMan
FreeMan

Reputation: 5687

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.

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.

Upvotes: 0

Comintern
Comintern

Reputation: 22205

You are using rngstart and rngend as if they were strings in your call to 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.

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

Upvotes: 3

Related Questions