Excel VBA - Convert string to range

Question

I have a function that stores a range.address in a cell. The value of that cell is IE: AQ43:BF44

How can I turn this string into a Range?

I have tried:

Dim Cell As Range
Dim pRange As String
Dim projectRange As Range
    pRange = Project.Range ' Stored as "AQ43:BF44" in this case
    projectRange = Range(pRange)

    For Each Cell In projectRange
        If TargetCell = Cell Then
           'Do something
        End If
    Next Cell

This code reports run-time error 91: Object variable or with block variable not set.

The main purpose of this function is to check if the targetCell(the cell I doubleclicked) is found within the stored range.address .

pRange = projectRange.Value and not pRange = Project.Range — Rory
– Rory, Commented Feb 11, 2016 at 9:21

user4039065 · Accepted Answer · 2016-02-11 09:47:46Z

^{The main purpose of this function is to check if the targetCell(the cell I doubleclicked[sic]) is found within the stored range.address}

You are looking to see if the Intersect method produces something that is within the range described by the text in a cell (e.g. AQ43:BF44 ) and a cell you double-click (e.g. Target ).

Option Explicit    '<~~ see footnote

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim pRange As String
    pRange = Worksheets("Sheet1").Range("A1").Value2
    'alternates
    'pRange = Worksheets("Sheet1").Range("A1").value
    'pRange = Worksheets("Sheet1").Range("A1").text
    If Not Intersect(Target, Range(pRange)) Is Nothing Then
        Cancel = False
        MsgBox Target.Address(0, 0) & " is within " & pRange
    End If
End Sub

You are seeking to retrieve text from a cell; possibly text that is returned from a formula involving something like the ADDRESS function. For this you can use the Range.Value property, Range.Text property or Range.Value2 property. Strictly speaking, the .Value2 property is the fastest but it isn't going to make a lot of difference here.

It's a little unclear on where the text (e.g. AQ43:BF44 ) resides. I've included parent worksheet as well as range definition that should be easy to edit.

A global solution could be achieved with the ThisWorkbook's Workbook_SheetBeforeDoubleClick event macro.

_{Setting Require Variable Declaration within the VBE's Tools ► Options ► Editor property page will put the Option Explicit statement at the top of each newly created code sheet. This will avoid silly coding mistakes like misspellings as well as influencing you to use the correct variable type in the variable declaration. Variables created on-the-fly without declaration are all of the variant/object type. Using Option Explicit is widely considered 'best practice'.}

Thanks. Using intersect solved the problem flawlessly. Had no problem working your solution into my worksheet.

Verzweifler · Accepted Answer · 2016-02-11 09:54:05Z

2

Since a Range is an "object" in Excel, you need to use the Set-keyword when assigning values:

Set projectRange = Range(pRange)

You will, however, encounter an error if the value of pRangedoesn't describe a valid Range.

Also, you might want to be a bit more specific when iterating over For Each Cell In projectRange. Depending on what you want to do, you could iterate over projectRange.Cells, projectRange.Rows, projectRange.Columns...

answered Feb 11, 2016 at 9:54

Verzweifler

9406 silver badges19 bronze badges

Collectives™ on Stack Overflow

Excel VBA - Convert string to range

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related