string to range

[gromit12]

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]

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]

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

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!