Yes, that's correct. A single cell is a range. And when you type 'Dim c as' a
list appears for us to choose from, and 'Cell' is not in it.
By the way, at times I have had problems not stating the sheet with a named
range, and don't know why it works sometimes and not others. I use trial and
error a lot.
Regards - Dave.
> Date: Fri, 27 Aug 2010 11:20:10 -0700
> Subject: Re: $$Excel-Macros$$ Code to enter value in multiple worksheets
> From: heislerk...@gmail.com
> To: excel-macros@googlegroups.com
>
> Wow! This works great. The advantage of not always having to specify
> the sheet when using Named Ranges never occurred to me.
>
> As for this line:
>
> c As Range
>
> I would have expected to see something like 'c As Cell', but I guess
> in Excel terms a cell is a range (albeit a very small one)?
>
> Thanks much.
>
> On Aug 26, 5:22 am, Dave Bonallack <davebonall...@hotmail.com> wrote:
> > Hi Kurt,
> >
> > If your named ranges are really called WS1Data, WS2Data, WS3Data, WS4Data,
> > then your code can be reduced to just a few lines.
> >
> > Sub NoData()
> > Dim i As Integer, c As Range
> > ' Cycle thru each Named Range
> > For i = 1 To 4
> > ' Cycle thru each cell within the Named Range
> > For Each c In Range("WS" & i & "Data")
> > If IsNull(c) Or c = "" Then c = "no data"
> > Next c
> > Next i
> > End Sub
> >
> > When you use Named Ranges, most of the time you don't need to state the
> > sheet name. In your case, we have 4, so we need only cycle through them,
> > and ignore the sheet names. This is one BIG advantage of using Named Ranges.
> >
> > Note that the 'i' from the 'For i = 1 to 4' thingy has also been used to
> > help construct the name of each range. Range("WS" & i & "Data")
> >
> > By the way, note that I've used 'c' as my variable to represent each cell
> > in the range. It's generally bad practice to use a word like 'cell' as a
> > variable, since VBA has already allotted it a special meaning. VBA might
> > pout if you use its favourite words for other things.
> >
> > Hope this helps.
> >
> > Regards - Dave.
> >
> > > Date: Wed, 25 Aug 2010 12:14:21 -0700
> > > Subject: $$Excel-Macros$$ Code to enter value in multiple worksheets
> > > From: heislerk...@gmail.com
> > > To: excel-macros@googlegroups.com
> >
> > > I have 4 worksheets (WS1, WS2, etc.) with cells that get populated
> > > with data from a linked source sheet. In Column B on each worksheet is
> > > a dynamic range of cells (on WS1 it's "WS1Data," WS2 it's "WS2Data,"
> > > etc.) When rows in the range are blank, I'd like to insert the word
> > > "no data."
> >
> > > I can do this with the following code, which manually selects the
> > > sheet, the cells, and the range, and then inserts "no data" in blank
> > > cells:
> >
> > > Sub InsertData()
> >
> > > ' Insert "no data" in empty cells
> >
> > > ' Do Worksheet 1
> >
> > > Sheets("WS1").Select
> > > Cells.Select ' is this line needed?
> > > Range("WS1Data").Activate
> >
> > > For Each Cell In Range("WS1Data")
> > > If IsNull(Cell.Value) Or Cell.Value = "" Then
> > > Cell.Value = "no data"
> > > End If
> > > Next
> >
> > > ' Do Worksheet 2
> >
> > > Sheets("WS2").Select
> > > Cells.Select
> > > Range("WS2Data").Activate
> >
> > > For Each Cell In Range("WS2Data")
> > > If IsNull(Cell.Value) Or Cell.Value = "" Then
> > > Cell.Value = "no data"
> > > End If
> > > Next
> >
> > > [etc. for the remaining two worksheets]
> >
> > > End Sub
> >
> > > #
> >
> > > As you can see, the code sets the focus to the corresponding worksheet
> > > as it goes through the code. Is there a more efficient way to perform
> > > this procedure, perhaps one that's transparent to the user? If not,
> > > I'd be interested in suggestions for making the code more economical.
> > > For instance, I suspect I don't need to rewrite the For ... Each
> > > segment four times, and instead just plug in a range value. Just not
> > > sure how.
> >
> > > Thank you.
> >
> > > --
> > > ----------------------------------------------------------------------------------
> > > Some important links for excel users:
> > > 1. Follow us on TWITTER for tips tricks and links
> > > :http://twitter.com/exceldailytip
> > > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
> >
> > > To post to this group, send email to excel-macros@googlegroups.com
> >
> > > <><><><><><><><><><><><><><><><><><><><><><>
> > > HELP US GROW !!
> >
> > > We reach over 7000 subscribers worldwide and receive many nice notes
> > > about the learning and support from the group.Let friends and co-workers
> > > know they can subscribe to group
> > > athttp://groups.google.com/group/excel-macros/subscribe
>
> --
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> HELP US GROW !!
>
> We reach over 7000 subscribers worldwide and receive many nice notes about
> the learning and support from the group.Let friends and co-workers know they
> can subscribe to group at
> http://groups.google.com/group/excel-macros/subscribe
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
