Public Sub TestRanges() 'Sort or Modify columns Macro
Sheets("Rockwell").Select
NamedRanges = Array("Scl1", "Scl2", "Scl3", "Scl4", "Scl5", "Scl6",
"Scl7", "Scl8", "Scl9", "Scl10")
For Each NRange In NamedRanges
Set isect = Application.Intersect(Range(NRange), ActiveCell)
If Not isect Is Nothing Then
Worksheets("Rockwell").Select
Load Rckwll
Rckwll.Show
Unload Rckwll
Exit For
End If
Next NRange
End Sub

Thanks to Joel for this code. It works great provided I select one of the
"Scls". I'd like to add a message that tells the user what to do if one of
the Scls isn't selected.
TIA
Lou

I'm getting old... Excel 2003

"Rookie 1st class" wrote:

> Public Sub TestRanges() 'Sort or Modify columns Macro
> Sheets("Rockwell").Select
> NamedRanges = Array("Scl1", "Scl2", "Scl3", "Scl4", "Scl5", "Scl6",
> "Scl7", "Scl8", "Scl9", "Scl10")
> For Each NRange In NamedRanges
> Set isect = Application.Intersect(Range(NRange), ActiveCell)
> If Not isect Is Nothing Then
> Worksheets("Rockwell").Select
> Load Rckwll
> Rckwll.Show
> Unload Rckwll
> Exit For
> End If
> Next NRange
> End Sub
>
> Thanks to Joel for this code. It works great provided I select one of the
> "Scls". I'd like to add a message that tells the user what to do if one of
> the Scls isn't selected.
> TIA
> Lou

For what you posted, try this...

Public Sub TestRanges() 'Sort or Modify columns Macro
Sheets("Rockwell").Select
NamedRanges = Array("Scl1", "Scl2", "Scl3", "Scl4", "Scl5", _
"Scl6", "Scl7", "Scl8", "Scl9", "Scl10")
For Each NRange In NamedRanges
Set isect = Application.Intersect(Range(NRange), ActiveCell)
If Not isect Is Nothing Then
Worksheets("Rockwell").Select
Load Rckwll
Rckwll.Show
Unload Rckwll
Exit Sub
End If
Next NRange
MsgBox "Nothing was selected!"
End Sub

However, given your Remark in the code, I'm guessing this is not all of the
macro's code, so perhaps you need this instead...

Public Sub TestRanges() 'Sort or Modify columns Macro
Sheets("Rockwell").Select
NamedRanges = Array("Scl1", "Scl2", "Scl3", "Scl4", "Scl5", _
"Scl6", "Scl7", "Scl8", "Scl9", "Scl10")
For Each NRange In NamedRanges
Set isect = Application.Intersect(Range(NRange), ActiveCell)
If Not isect Is Nothing Then
Worksheets("Rockwell").Select
Load Rckwll
Rckwll.Show
Unload Rckwll
GoTo Continue
End If
Next NRange
MsgBox "Nothing was selected!"
Continue:
'
' << Rest of your code goes here >>
'
End Sub

--
Rick (MVP - Excel)


"Rookie 1st class" <Rookie1stClass@SpamThis> wrote in message
news:457E10B6-444F-4441-8181-(E-Mail Removed)...
> Public Sub TestRanges() 'Sort or Modify columns Macro
> Sheets("Rockwell").Select
> NamedRanges = Array("Scl1", "Scl2", "Scl3", "Scl4", "Scl5", "Scl6",
> "Scl7", "Scl8", "Scl9", "Scl10")
> For Each NRange In NamedRanges
> Set isect = Application.Intersect(Range(NRange), ActiveCell)
> If Not isect Is Nothing Then
> Worksheets("Rockwell").Select
> Load Rckwll
> Rckwll.Show
> Unload Rckwll
> Exit For
> End If
> Next NRange
> End Sub
>
> Thanks to Joel for this code. It works great provided I select one of the
> "Scls". I'd like to add a message that tells the user what to do if one of
> the Scls isn't selected.
> TIA
> Lou

Thanks guys.
I had a basic knowledge of what to do just not certain of where.
Final code below.
Lou
Public Sub TestRanges() 'Sort or Modify columns Macro
Sheets("Rockwell").Select
NamedRanges = Array("Scl1", "Scl2", "Scl3", "Scl4", "Scl5", "Scl6",
"Scl7", "Scl8", "Scl9", "Scl10")
For Each NRange In NamedRanges
Set isect = Application.Intersect(Range(NRange), ActiveCell)
If Not isect Is Nothing Then
Worksheets("Rockwell").Select
Load Rckwll
Rckwll.Show
Unload Rckwll
Exit For
End If
Next NRange
MsgBox "Select one of the ""Scale under test"" blocks."
End Sub


"Simon Lloyd" wrote:

>
> Maybe add an Else to your IF like this:
> Public Sub TestRanges() 'Sort or Modify columns Macro
> Dim mBox As Variant
> Dim Nxt As Variant
> Nxt:
> Sheets("Rockwell").Select
> NamedRanges = Array("Scl1", "Scl2", "Scl3", "Scl4", "Scl5", "Scl6",
> "Scl7", "Scl8", "Scl9", "Scl10")
> For Each NRange In NamedRanges
> Set isect = Application.Intersect(Range(NRange), ActiveCell)
> If Not isect Is Nothing Then
> Worksheets("Rockwell").Select
> Load Rckwll
> Rckwll.Show
> Unload Rckwll
> Exit For
> Else
> mBox = MsgBox("You have not selected a range", vbYesNo, "Range not
> detected")
> If mBox = vbYes Then GoTo Nxt
> End If
> Next NRange
> End Sub
>
>
> Rookie 1st class;289973 Wrote:
> > Public Sub TestRanges() 'Sort or Modify columns Macro
> > Sheets("Rockwell").Select
> > NamedRanges = Array("Scl1", "Scl2", "Scl3", "Scl4", "Scl5", "Scl6",
> > "Scl7", "Scl8", "Scl9", "Scl10")
> > For Each NRange In NamedRanges
> > Set isect = Application.Intersect(Range(NRange), ActiveCell)
> > If Not isect Is Nothing Then
> > Worksheets("Rockwell").Select
> > Load Rckwll
> > Rckwll.Show
> > Unload Rckwll
> > Exit For
> > End If
> > Next NRange
> > End Sub
> >
> > Thanks to Joel for this code. It works great provided I select one of
> > the
> > "Scls". I'd like to add a message that tells the user what to do if one
> > of
> > the Scls isn't selected.
> > TIA
> > Lou
>
>
> --
> Simon Lloyd
>
> Regards,
> Simon Lloyd
> 'The Code Cage' (http://www.thecodecage.com)
> ------------------------------------------------------------------------
> Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
> View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=81040
>
>

Final - Final
It flashed the MsgBox even if one of the Arrays was selected. An additional
Exit For corrected that. "Flashes HUGE Grin"

Public Sub TestRanges() 'Sort or Modify columns Macro
Sheets("Rockwell").Select
NamedRanges = Array("Scl1", "Scl2", "Scl3", "Scl4", "Scl5", "Scl6",
"Scl7", "Scl8", "Scl9", "Scl10")
For Each NRange In NamedRanges
Set isect = Application.Intersect(Range(NRange), ActiveCell)
If Not isect Is Nothing Then
Worksheets("Rockwell").Select
Load Rckwll
Rckwll.Show
Unload Rckwll
Exit For
Else
MsgBox "Select one of the ""Scale under test"" blocks, then run
again."
Exit For
End If
Next NRange
End Sub

I owe Joel, Simon, and Rick a beverage of their choice
Many Thanks Guys
Lou

You didn't copy my code as I posted it (you should always consider
copy/pasting rather than re-typing)... I didn't have an Exit For... I had an
Exit Sub in the If..Then block. I'm pretty sure the code I posted would have
worked as written. In any event, you have working code now, which is all
that matters. I'll be sure to have an online cup of coffee on you...
thanks.<g>

--
Rick (MVP - Excel)


"Rookie 1st class" <Rookie1stClass@SpamThis> wrote in message
news:B8AB1789-030B-4D8F-9B72-(E-Mail Removed)...
> Final - Final
> It flashed the MsgBox even if one of the Arrays was selected. An
> additional
> Exit For corrected that. "Flashes HUGE Grin"
>
> Public Sub TestRanges() 'Sort or Modify columns Macro
> Sheets("Rockwell").Select
> NamedRanges = Array("Scl1", "Scl2", "Scl3", "Scl4", "Scl5", "Scl6",
> "Scl7", "Scl8", "Scl9", "Scl10")
> For Each NRange In NamedRanges
> Set isect = Application.Intersect(Range(NRange), ActiveCell)
> If Not isect Is Nothing Then
> Worksheets("Rockwell").Select
> Load Rckwll
> Rckwll.Show
> Unload Rckwll
> Exit For
> Else
> MsgBox "Select one of the ""Scale under test"" blocks, then run
> again."
> Exit For
> End If
> Next NRange
> End Sub
>
> I owe Joel, Simon, and Rick a beverage of their choice
> Many Thanks Guys
> Lou

Bows to Rick. Your code worked properly.
I owe you 2 beverages of your choice.
Thanks again
Lou

"Rick Rothstein" wrote:

> You didn't copy my code as I posted it (you should always consider
> copy/pasting rather than re-typing)... I didn't have an Exit For... I had an
> Exit Sub in the If..Then block. I'm pretty sure the code I posted would have
> worked as written. In any event, you have working code now, which is all
> that matters. I'll be sure to have an online cup of coffee on you...
> thanks.<g>
>
> --
> Rick (MVP - Excel)
>
>
> "Rookie 1st class" <Rookie1stClass@SpamThis> wrote in message
> news:B8AB1789-030B-4D8F-9B72-(E-Mail Removed)...
> > Final - Final
> > It flashed the MsgBox even if one of the Arrays was selected. An
> > additional
> > Exit For corrected that. "Flashes HUGE Grin"
> >
> > Public Sub TestRanges() 'Sort or Modify columns Macro
> > Sheets("Rockwell").Select
> > NamedRanges = Array("Scl1", "Scl2", "Scl3", "Scl4", "Scl5", "Scl6",
> > "Scl7", "Scl8", "Scl9", "Scl10")
> > For Each NRange In NamedRanges
> > Set isect = Application.Intersect(Range(NRange), ActiveCell)
> > If Not isect Is Nothing Then
> > Worksheets("Rockwell").Select
> > Load Rckwll
> > Rckwll.Show
> > Unload Rckwll
> > Exit For
> > Else
> > MsgBox "Select one of the ""Scale under test"" blocks, then run
> > again."
> > Exit For
> > End If
> > Next NRange
> > End Sub
> >
> > I owe Joel, Simon, and Rick a beverage of their choice
> > Many Thanks Guys
> > Lou
>
>