string to range
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!
|