Hi folks,

Sorry, it's been a while since I've done this and can't seem to find
the question answered elsewhere. Basically I have a line of code that
creates a string from a variable. What I'm struggling with is to set a
range object variable to the range suggested by the string.

Sub Test (sColLetter As String)
Dim rng As Range
Dim sRng As String

sRng = "ThisWorkbook.Sheets(" & Chr(34) & "InputMatrix" & Chr(34) &
").Range(" & Chr(34) & sColLetter & "7:" & sColLetter & "110" &
Chr(34) & ")"

Set rng = ?

End Sub

If sColLetter happens to be "F", then sRng reads:
ThisWorkbook.Sheets("InputMatrix").Range("E7:E110" )

I've tried Set rng = Range(sRng) to no avail....

Any help much appreciated...

First, this seems like a pretty unusual question.

I take it that you can't use something like:

Option Explicit
Sub Test(sColLetter As String)
Dim rng As Range

Set rng = ThisWorkbook.Worksheets("inputmatrix") _
.Cells(7, sColLetter).Resize(94, 1)

Debug.Print rng.Address(external:=True)

End Sub
Sub test1()
Call Test("F")
End Sub

==========
I'm gonna guess that you're parsing/building something else and it's a lot more
painful than the simple example you showed.

If that's true, then maybe this would help:

Option Explicit
Sub Test(sColLetter As String)
Dim rng As Range
Dim sRng As String

sRng = "'[" & ThisWorkbook.Name & "]inputmatrix'!" _
& sColLetter & "7:" & sColLetter & "110"

Set rng = Application.Range(sRng)

Debug.Print rng.Address(external:=True)

End Sub
Sub test2()
Call Test("F")
End Sub

Notice that it's using application.range()--not just Range().

=============
This line:
Set rng = ThisWorkbook.Worksheets("inputmatrix") _
.Cells(7, sColLetter).Resize(94, 1)
could also be written as:

with thisworkbook.worksheets("inputmatrix")
Set rng = .range(.Cells(7, sColLetter), .cells(110, sColLetter))
end with

or

set rng = thisworkbook.worksheets("inputmatrix") _
.range(sColLetter & "7:" & sColLetter & 110)

Whichever you find the easiest to modify...




gromit12 wrote:

Hi folks,

Sorry, it's been a while since I've done this and can't seem to find
the question answered elsewhere. Basically I have a line of code that
creates a string from a variable. What I'm struggling with is to set a
range object variable to the range suggested by the string.

Sub Test (sColLetter As String)
Dim rng As Range
Dim sRng As String

sRng = "ThisWorkbook.Sheets(" & Chr(34) & "InputMatrix" & Chr(34) &
").Range(" & Chr(34) & sColLetter & "7:" & sColLetter & "110" &
Chr(34) & ")"

Set rng = ?

End Sub

If sColLetter happens to be "F", then sRng reads:
ThisWorkbook.Sheets("InputMatrix").Range("E7:E110" )

I've tried Set rng = Range(sRng) to no avail....

Any help much appreciated...

--

Dave Peterson

On Feb 18, 5:25*pm, Dave Peterson wrote:
First, this seems like a pretty unusual question.

I take it that you can't use something like:

Option Explicit
Sub Test(sColLetter As String)
* * Dim rng As Range

* * Set rng = ThisWorkbook.Worksheets("inputmatrix") _
* * * * * * * * * .Cells(7, sColLetter).Resize(94, 1)

* * Debug.Print rng.Address(external:=True)

End Sub
Sub test1()
* * Call Test("F")
End Sub

==========
I'm gonna guess that you're parsing/building something else and it's a lot more
painful than the simple example you showed.

If that's true, then maybe this would help:

Option Explicit
Sub Test(sColLetter As String)
* Dim rng As Range
* Dim sRng As String

* sRng = "'[" & ThisWorkbook.Name & "]inputmatrix'!" _
* * * * * & sColLetter & "7:" & sColLetter & "110"

* Set rng = Application.Range(sRng)

* Debug.Print rng.Address(external:=True)

End Sub
Sub test2()
* * Call Test("F")
End Sub

Notice that it's using application.range()--not just Range().

=============
This line:
* * Set rng = ThisWorkbook.Worksheets("inputmatrix") _
* * * * * * * * * .Cells(7, sColLetter).Resize(94, 1)
could also be written as:

with thisworkbook.worksheets("inputmatrix")
* * Set rng = .range(.Cells(7, sColLetter), .cells(110, sColLetter))
end with

or

set rng = thisworkbook.worksheets("inputmatrix") _
* * * * * * .range(sColLetter & "7:" & sColLetter & 110)

Whichever you find the easiest to modify...





gromit12 wrote:

Hi folks,

Sorry, it's been a while since I've done this and can't seem to find
the question answered elsewhere. Basically I have a line of code that
creates a string from a variable. What I'm struggling with is to set a
range object variable to the range suggested by the string.

Sub Test (sColLetter As String)
Dim rng As Range
Dim sRng As String

sRng = "ThisWorkbook.Sheets(" & Chr(34) & "InputMatrix" & Chr(34) &
").Range(" & Chr(34) & sColLetter & "7:" & sColLetter & "110" &
Chr(34) & ")"

Set rng = ?

End Sub

If sColLetter happens to be "F", then sRng reads:
ThisWorkbook.Sheets("InputMatrix").Range("E7:E110" )

I've tried Set rng = Range(sRng) to no avail....

Any help much appreciated...

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave, thank you very much for the help with this. Both options are an
education for me. Much appreciated!