Re: Declaring Objects

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Bob Phillips (bob.phillips_at_notheretiscali.co.uk)
Date: 03/14/05 

Date: Mon, 14 Mar 2005 09:26:55 -0000

Sharon,

The problem seems to lie with the line

Set Colors = Colors.Range("B1:Q400", "S1:AI400")

What this is saying is set the Colors variable to refer to the Colors ***,
ranges ("B1:Q400" and "S1:AI400". Note that it is the worksheets codename
that you are using, not its Excel name. If you really have a work***
codename of Colors, you have a conflict as you have 2 objects with the same
name. You can either use

Set Colors = Range("B1:Q400", "S1:AI400")

if you just want to pick up the active***, or

Set Colors = Worksheets("Colors").Range("B1:Q400", "S1:AI400")

if you want to refer explicitly to a work*** (a good practice) with the
Excel name of Colors, or

Set Colors = wsColors.Range("B1:Q400", "S1:AI400")

if you set the work*** codename to wsColors.

BTW, this sort of conflict is partly why many of us use naming conventions
for our variables, and objects. I use sName for string variables, cName for
counting variables, iNAme for indexing variables, and oName for objects. For
objects , I sometimes further qualify like oRngName, or oWsName, for greater
clarity. 

-- 
HTH
RP
(remove nothere from the email address if mailing direct)
"Sharon" <Sharon@discussions.microsoft.com> wrote in message
news:3C6B22C0-B426-45D2-9810-AF4AE29D1A35@microsoft.com...
> I hope this post makes sense.  I am trying to learn VB on my own and I
know
> that I need to declare my objects and know that I would prefer to declare
> them early in the code.
>
> I want to write a macro that does a find and replace for "Green",
"Yellow",
> "Red", and "No" on Sheet1 and replaces the words with the values 1, 2, 3,
4,
> respectively.  The range of my data is from B1:Q400 and S1:AI400.  I named
> the non-contiguous range "Colors".  I would like to use the name of my
range
> in my code.
>
> Below is my first attempt to writing a macro, partially using the macro
> recorder.  The code the recorder gave me was:
>
> Sub FindAndReplace()
>
>     Selection.Replace What:="green", Replacement:="1", LookAt:=xlPart, _
>         SearchOrder:=xlByRows, MatchCase:=False
>     Selection.Replace What:="yellow", Replacement:="2", LookAt:=xlPart, _
>         SearchOrder:=xlByRows, MatchCase:=False
>     Selection.Replace What:="red", Replacement:="3", LookAt:=xlPart, _
>         SearchOrder:=xlByRows, MatchCase:=False
>     Selection.Replace What:="no", Replacement:="4", LookAt:=xlPart, _
>         SearchOrder:=xlByRows, MatchCase:=False
>
> End Sub
>
> Since I don't want the user to have to select the range each time to run
the
> macro, I want to change the code to apply to the range I named "Colors".
So
> here's my first attempt at declaring my "Colors" range as my object.
Except,
> since I don't really know what I'm doing, it doesn't work.
>
> Can anyone help me declare my range "Colors"?
>
> Sub FindAndReplace()
>
> Dim Colors As Range
> Set Colors = Colors.Range("B1:Q400", "S1:AI400")
>
>     Colors.Replace What:="green", Replacement:="1", LookAt:=xlPart, _
>         SearchOrder:=xlByRows, MatchCase:=False
>     Colors.Replace What:="yellow", Replacement:="2", LookAt:=xlPart, _
>         SearchOrder:=xlByRows, MatchCase:=False
>     Colors.Replace What:="red", Replacement:="3", LookAt:=xlPart, _
>         SearchOrder:=xlByRows, MatchCase:=False
>     Colors.Replace What:="no", Replacement:="4", LookAt:=xlPart, _
>         SearchOrder:=xlByRows, MatchCase:=False
> End Sub
>
> Thanks so much,
>
> Sharon
>
Quantcast