Excel Ranges and Areas Problem

[Anne]

We need to set the range from text sent to us. Below is an exmple. Can
anyone help as the issue is that normally the range the example below only
references cells [C4, B2, D8] in a sheet and not all the sheets and cells
[Sheet1!C4, Sheet2!B2, Sheet3!D8].

Sub test()
On Error GoTo iErrors

Dim strText As String
strText = "Sheet1!C4, Sheet2!B2, Sheet3!D8"

Dim rngTest As Excel.range

Set rngTest = Application.range("Sheet1!C4, Sheet2!B2, Sheet3!D8")
Debug.Print rngTest.Address
Exit Sub

iErrors:
Err.Clear
End Sub

'We need the rngTest.Address to reflect the different ranges in the
different sheets, namely Sheet1!C4, Sheet2!B2, Sheet3!D8.

Thanks in advancce.
Howard

 

[Dave Peterson]

A range in excel is on one worksheet.

But I don't understand what you're really trying to do.

We need to set the range from text sent to us. Below is an exmple. Can
anyone help as the issue is that normally the range the example below only
references cells [C4, B2, D8] in a sheet and not all the sheets and cells
[Sheet1!C4, Sheet2!B2, Sheet3!D8].

Sub test()
On Error GoTo iErrors

Dim strText As String
strText = "Sheet1!C4, Sheet2!B2, Sheet3!D8"

Dim rngTest As Excel.range

Set rngTest = Application.range("Sheet1!C4, Sheet2!B2, Sheet3!D8")
Debug.Print rngTest.Address
Exit Sub

iErrors:
Err.Clear
End Sub

'We need the rngTest.Address to reflect the different ranges in the
different sheets, namely Sheet1!C4, Sheet2!B2, Sheet3!D8.

Thanks in advancce.
Howard

 

[Puppet_Sock]

We need to set the range from text sent to us. Below is an exmple. Can
anyone help as the issue is that normally the range the example below only
references cells [C4, B2, D8] in a sheet and not all the sheets and cells
[Sheet1!C4, Sheet2!B2, Sheet3!D8].

Sub test()
On Error GoTo iErrors

Dim strText As String
strText = "Sheet1!C4, Sheet2!B2, Sheet3!D8"

Dim rngTest As Excel.range

Set rngTest = Application.range("Sheet1!C4, Sheet2!B2, Sheet3!D8")
Debug.Print rngTest.Address
Exit Sub

iErrors:
Err.Clear
End Sub

'We need the rngTest.Address to reflect the different ranges in the
different sheets, namely Sheet1!C4, Sheet2!B2, Sheet3!D8.

Thanks in advancce.
Howard

Curious. Your poster name is Anne, yet you sign off as Howard.
Oh well...

For future reference, source code is a poor media for communicating
your requirements. I can't really figure out what it is you are trying
to do from your source, nor from your description. You seem to want
data from different cells on different sheets. That's about as much
as I can figure out.

Can you explain a little more clearly? Do you want a range that
includes one cell from one sheet, a second cell from another sheet,
and a third cell form a third sheet? It might be easier to make an
array of ranges, one per sheet.
Socks

 

[Bernie Deitrick]

Anne,

Try it this way.... written for code within Excel, not an external program referencing Excel....

HTH,
Bernie
MS Excel MVP


Sub test()

Dim rngTest As Range
Dim strText As String
Dim myAdd As Variant
Dim i As Integer
Dim myShtName As String
Dim myR As String

On Error GoTo iErrors

strText = "Sheet1!C4, Sheet2!B2, Sheet3!D8"

myAdd = Split(strText, ",")

For i = LBound(myAdd) To UBound(myAdd)
myShtName = Left(Trim(myAdd(i)), InStr(1, Trim(myAdd(i)), "!") - 1)
myR = Mid(Trim(myAdd(i)), InStr(1, Trim(myAdd(i)), "!") + 1, Len(Trim(myAdd(i))))
Set rngTest = Worksheets(myShtName).Range(myR)
MsgBox rngTest.Address(, , , True)
Next i
Exit Sub

iErrors:
Err.Clear
End Sub