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
