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 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
