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

Question

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")

Answer 1

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