Thread: string to range
View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Dave Peterson Dave Peterson is offline
external usenet poster
 
Posts: 35,218
Default string to range

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