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
