CODEWITHSUNDEEP
vbaexcel
Chris
Chris

Reputation: 185

Excel VBA - Convert string to range

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 .

Upvotes: 0

Views: 8184

Answers (2)

Verzweifler
Verzweifler

Reputation: 940

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...

Upvotes: 2

user4039065
user4039065

Reputation:

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'.

Upvotes: 2

Related Questions