Subject: Setting a range from a refedit control

[Arne]

Hello,

On a form I have a refedit control wich is then used to set a range variable
for further processing:

Dim s As String
Dim rng as Range
s = refCtrl.Value
On Error Resume Next
Set rng = Range(s)
If Err.Number <> 0 Then
...

This works fine as long as the range selected using the refedit control
consists of one area. However, it fails on multiple-area ranges. Is there a
simple way of setting the range in this case without having to write an
extensive procedure in which the string s is analyzed and the areas are added
to the range object?

TIA, Arne

 

[Bernie Deitrick]

Arne,

The problem is with the code that you didn't post.

This works fine:

Dim s As String
Dim rng As Range
s = refCtrl.Value
On Error Resume Next
Set rng = Range(s)
MsgBox "The select range is " & rng.Address & Chr(10) & _
"It has " & rng.Areas.Count & " area(s), and a total of " & _
rng.Cells.Count & " cell(s)."

What are you doing AFTER this?


HTH,
Bernie
MS Excel MVP

 

[Arne]

Well, that is strange, because it does not work on my system. I omitted the
rest because Err.Number IS not equal to 0, but 1004. After the Err.Number<>0
statement I display a message and abort the routine. If I do add the message
box you propose, it is not displayed and rng remains equal to NOTHING. I am
using Excel 2002 and 2003, maybe that makes a difference (I doubt it)?

 

[Bernie Deitrick]

I'm using XL XP (2002).

Perhaps it is due to a separator setting? (Since VBA is US-centric, it could be that you have a
semicolon (or other separator) instead of a comma between the two ranges, and Range expects a
comma....)

Add the line

Range("A1").Value = s

just after

s = refCtrl.Value

(to make it easy to copy and paste into a message)

What is the value of s when the code fails?

If it does have a semicolon - for example:

'Sheet 1'!$B$4:$B$8;'Sheet 1'!$B$19:$B$23

Then perhaps adding

s = Replace(s,";",",")

will help....


HTH,
Bernie
MS Excel MVP

 

[Arne]

As soon as I read it, I knew that had to be it!
I inserted
s = Replace(s, Application.International(xlListSeparator), ",")
and evrything works fine!

Thanks!