|
Declaring Objects
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 |
Declaring Objects
Sharon
Either Colors=Range("colors") ' assumes you have named your range as "colors" or colors=Range("B1:Q400", "S1:AI400") HTH |
Declaring Objects
To clarify , you still need
Set colors = ...... Sorry! |
Declaring Objects
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 sheet, 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 worksheet 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 activesheet, or Set Colors = Worksheets("Colors").Range("B1:Q400", "S1:AI400") if you want to refer explicitly to a worksheet (a good practice) with the Excel name of Colors, or Set Colors = wsColors.Range("B1:Q400", "S1:AI400") if you set the worksheet 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" wrote in message ... 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 |
Declaring Objects
Your explanation was wonderful! Thank you so much! Here's what I did (and
it works, except for one little quirk) :) Sub FindAndReplace() Dim oColors As Range Set oColors = Worksheets("sheet1").Range("B1:Q400", "T1:AI400") oColors.Replace What:="green", Replacement:="1", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False oColors.Replace What:="yellow", Replacement:="2", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False oColors.Replace What:="red", Replacement:="3", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False oColors.Replace What:="no", Replacement:="4", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False End Sub The little quirk is that I have a Key in column R6:R10 that says: Green = 1 Yellow = 2 Red = 3 No = 4 When I run the macro, it changes the words, "Green", "Yellow", "Red", and "No" to numbers as well, even though that range of cells is not defined in my range. Any idea how to make it stop doing that? Grateful for help, Sharon "Bob Phillips" wrote: 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 sheet, 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 worksheet 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 activesheet, or Set Colors = Worksheets("Colors").Range("B1:Q400", "S1:AI400") if you want to refer explicitly to a worksheet (a good practice) with the Excel name of Colors, or Set Colors = wsColors.Range("B1:Q400", "S1:AI400") if you set the worksheet 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" wrote in message ... 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 |
Declaring Objects
The problem is this line
Set oColors = Worksheets("sheet1").Range("B1:Q400", "T1:AI400") which although it looks as though it picks two separate areas it actually picks one area of B1:AI400, which includes R6:R10 (and R:R5 and R7:R400). What you want is With Worksheets("sheet1") Set oColors = Union(.Range("B1:Q400"), Range("T1:AI400")) End With instead of that line, which creates a union of the two areas, and then works on that. -- HTH RP (remove nothere from the email address if mailing direct) "Sharon" wrote in message ... Your explanation was wonderful! Thank you so much! Here's what I did (and it works, except for one little quirk) :) Sub FindAndReplace() Dim oColors As Range Set oColors = Worksheets("sheet1").Range("B1:Q400", "T1:AI400") oColors.Replace What:="green", Replacement:="1", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False oColors.Replace What:="yellow", Replacement:="2", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False oColors.Replace What:="red", Replacement:="3", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False oColors.Replace What:="no", Replacement:="4", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False End Sub The little quirk is that I have a Key in column R6:R10 that says: Green = 1 Yellow = 2 Red = 3 No = 4 When I run the macro, it changes the words, "Green", "Yellow", "Red", and "No" to numbers as well, even though that range of cells is not defined in my range. Any idea how to make it stop doing that? Grateful for help, Sharon "Bob Phillips" wrote: 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 sheet, 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 worksheet 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 activesheet, or Set Colors = Worksheets("Colors").Range("B1:Q400", "S1:AI400") if you want to refer explicitly to a worksheet (a good practice) with the Excel name of Colors, or Set Colors = wsColors.Range("B1:Q400", "S1:AI400") if you set the worksheet 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" wrote in message ... 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 |
Declaring Objects
Or even just:
Dim oColors As Range Set oColors = Worksheets("sheet1").Range("B1:Q400,T1:AI400") MsgBox oColors.Address Bob Phillips wrote: The problem is this line Set oColors = Worksheets("sheet1").Range("B1:Q400", "T1:AI400") which although it looks as though it picks two separate areas it actually picks one area of B1:AI400, which includes R6:R10 (and R:R5 and R7:R400). What you want is With Worksheets("sheet1") Set oColors = Union(.Range("B1:Q400"), Range("T1:AI400")) End With instead of that line, which creates a union of the two areas, and then works on that. -- HTH RP (remove nothere from the email address if mailing direct) "Sharon" wrote in message ... Your explanation was wonderful! Thank you so much! Here's what I did (and it works, except for one little quirk) :) Sub FindAndReplace() Dim oColors As Range Set oColors = Worksheets("sheet1").Range("B1:Q400", "T1:AI400") oColors.Replace What:="green", Replacement:="1", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False oColors.Replace What:="yellow", Replacement:="2", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False oColors.Replace What:="red", Replacement:="3", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False oColors.Replace What:="no", Replacement:="4", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False End Sub The little quirk is that I have a Key in column R6:R10 that says: Green = 1 Yellow = 2 Red = 3 No = 4 When I run the macro, it changes the words, "Green", "Yellow", "Red", and "No" to numbers as well, even though that range of cells is not defined in my range. Any idea how to make it stop doing that? Grateful for help, Sharon "Bob Phillips" wrote: 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 sheet, 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 worksheet 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 activesheet, or Set Colors = Worksheets("Colors").Range("B1:Q400", "S1:AI400") if you want to refer explicitly to a worksheet (a good practice) with the Excel name of Colors, or Set Colors = wsColors.Range("B1:Q400", "S1:AI400") if you set the worksheet 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" wrote in message ... 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 -- Dave Peterson |
Declaring Objects
Thanks! It works beautifully.
On to bigger and better problems..... Sharon :) "Bob Phillips" wrote: The problem is this line Set oColors = Worksheets("sheet1").Range("B1:Q400", "T1:AI400") which although it looks as though it picks two separate areas it actually picks one area of B1:AI400, which includes R6:R10 (and R:R5 and R7:R400). What you want is With Worksheets("sheet1") Set oColors = Union(.Range("B1:Q400"), Range("T1:AI400")) End With instead of that line, which creates a union of the two areas, and then works on that. -- HTH RP (remove nothere from the email address if mailing direct) "Sharon" wrote in message ... Your explanation was wonderful! Thank you so much! Here's what I did (and it works, except for one little quirk) :) Sub FindAndReplace() Dim oColors As Range Set oColors = Worksheets("sheet1").Range("B1:Q400", "T1:AI400") oColors.Replace What:="green", Replacement:="1", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False oColors.Replace What:="yellow", Replacement:="2", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False oColors.Replace What:="red", Replacement:="3", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False oColors.Replace What:="no", Replacement:="4", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False End Sub The little quirk is that I have a Key in column R6:R10 that says: Green = 1 Yellow = 2 Red = 3 No = 4 When I run the macro, it changes the words, "Green", "Yellow", "Red", and "No" to numbers as well, even though that range of cells is not defined in my range. Any idea how to make it stop doing that? Grateful for help, Sharon "Bob Phillips" wrote: 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 sheet, 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 worksheet 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 activesheet, or Set Colors = Worksheets("Colors").Range("B1:Q400", "S1:AI400") if you want to refer explicitly to a worksheet (a good practice) with the Excel name of Colors, or Set Colors = wsColors.Range("B1:Q400", "S1:AI400") if you set the worksheet 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" wrote in message ... 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 |
Declaring Objects
I tried this one too and it worked nicely also. I think I'll go with the one
without the message box since this will be used by other people and I think the message box will confuse them. Thanks for your input! Sharon "Dave Peterson" wrote: Or even just: Dim oColors As Range Set oColors = Worksheets("sheet1").Range("B1:Q400,T1:AI400") MsgBox oColors.Address Bob Phillips wrote: The problem is this line Set oColors = Worksheets("sheet1").Range("B1:Q400", "T1:AI400") which although it looks as though it picks two separate areas it actually picks one area of B1:AI400, which includes R6:R10 (and R:R5 and R7:R400). What you want is With Worksheets("sheet1") Set oColors = Union(.Range("B1:Q400"), Range("T1:AI400")) End With instead of that line, which creates a union of the two areas, and then works on that. -- HTH RP (remove nothere from the email address if mailing direct) "Sharon" wrote in message ... Your explanation was wonderful! Thank you so much! Here's what I did (and it works, except for one little quirk) :) Sub FindAndReplace() Dim oColors As Range Set oColors = Worksheets("sheet1").Range("B1:Q400", "T1:AI400") oColors.Replace What:="green", Replacement:="1", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False oColors.Replace What:="yellow", Replacement:="2", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False oColors.Replace What:="red", Replacement:="3", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False oColors.Replace What:="no", Replacement:="4", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False End Sub The little quirk is that I have a Key in column R6:R10 that says: Green = 1 Yellow = 2 Red = 3 No = 4 When I run the macro, it changes the words, "Green", "Yellow", "Red", and "No" to numbers as well, even though that range of cells is not defined in my range. Any idea how to make it stop doing that? Grateful for help, Sharon "Bob Phillips" wrote: 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 sheet, 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 worksheet 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 activesheet, or Set Colors = Worksheets("Colors").Range("B1:Q400", "S1:AI400") if you want to refer explicitly to a worksheet (a good practice) with the Excel name of Colors, or Set Colors = wsColors.Range("B1:Q400", "S1:AI400") if you set the worksheet 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" wrote in message ... 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 -- Dave Peterson |
Declaring Objects
The message box was there just to show you that it worked. You don't need to
included it in your code. Sharon wrote: I tried this one too and it worked nicely also. I think I'll go with the one without the message box since this will be used by other people and I think the message box will confuse them. Thanks for your input! Sharon "Dave Peterson" wrote: Or even just: Dim oColors As Range Set oColors = Worksheets("sheet1").Range("B1:Q400,T1:AI400") MsgBox oColors.Address Bob Phillips wrote: The problem is this line Set oColors = Worksheets("sheet1").Range("B1:Q400", "T1:AI400") which although it looks as though it picks two separate areas it actually picks one area of B1:AI400, which includes R6:R10 (and R:R5 and R7:R400). What you want is With Worksheets("sheet1") Set oColors = Union(.Range("B1:Q400"), Range("T1:AI400")) End With instead of that line, which creates a union of the two areas, and then works on that. -- HTH RP (remove nothere from the email address if mailing direct) "Sharon" wrote in message ... Your explanation was wonderful! Thank you so much! Here's what I did (and it works, except for one little quirk) :) Sub FindAndReplace() Dim oColors As Range Set oColors = Worksheets("sheet1").Range("B1:Q400", "T1:AI400") oColors.Replace What:="green", Replacement:="1", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False oColors.Replace What:="yellow", Replacement:="2", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False oColors.Replace What:="red", Replacement:="3", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False oColors.Replace What:="no", Replacement:="4", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False End Sub The little quirk is that I have a Key in column R6:R10 that says: Green = 1 Yellow = 2 Red = 3 No = 4 When I run the macro, it changes the words, "Green", "Yellow", "Red", and "No" to numbers as well, even though that range of cells is not defined in my range. Any idea how to make it stop doing that? Grateful for help, Sharon "Bob Phillips" wrote: 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 sheet, 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 worksheet 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 activesheet, or Set Colors = Worksheets("Colors").Range("B1:Q400", "S1:AI400") if you want to refer explicitly to a worksheet (a good practice) with the Excel name of Colors, or Set Colors = wsColors.Range("B1:Q400", "S1:AI400") if you set the worksheet 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" wrote in message ... 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 -- Dave Peterson -- Dave Peterson |
Declaring Objects
Sharon,
I don't think Dave was suggesting that you use MsgBox, he just included that so that you could verify it works (in testing) :-) -- HTH RP (remove nothere from the email address if mailing direct) "Sharon" wrote in message ... I tried this one too and it worked nicely also. I think I'll go with the one without the message box since this will be used by other people and I think the message box will confuse them. Thanks for your input! Sharon "Dave Peterson" wrote: Or even just: Dim oColors As Range Set oColors = Worksheets("sheet1").Range("B1:Q400,T1:AI400") MsgBox oColors.Address Bob Phillips wrote: The problem is this line Set oColors = Worksheets("sheet1").Range("B1:Q400", "T1:AI400") which although it looks as though it picks two separate areas it actually picks one area of B1:AI400, which includes R6:R10 (and R:R5 and R7:R400). What you want is With Worksheets("sheet1") Set oColors = Union(.Range("B1:Q400"), Range("T1:AI400")) End With instead of that line, which creates a union of the two areas, and then works on that. -- HTH RP (remove nothere from the email address if mailing direct) "Sharon" wrote in message ... Your explanation was wonderful! Thank you so much! Here's what I did (and it works, except for one little quirk) :) Sub FindAndReplace() Dim oColors As Range Set oColors = Worksheets("sheet1").Range("B1:Q400", "T1:AI400") oColors.Replace What:="green", Replacement:="1", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False oColors.Replace What:="yellow", Replacement:="2", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False oColors.Replace What:="red", Replacement:="3", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False oColors.Replace What:="no", Replacement:="4", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False End Sub The little quirk is that I have a Key in column R6:R10 that says: Green = 1 Yellow = 2 Red = 3 No = 4 When I run the macro, it changes the words, "Green", "Yellow", "Red", and "No" to numbers as well, even though that range of cells is not defined in my range. Any idea how to make it stop doing that? Grateful for help, Sharon "Bob Phillips" wrote: 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 sheet, 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 worksheet 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 activesheet, or Set Colors = Worksheets("Colors").Range("B1:Q400", "S1:AI400") if you want to refer explicitly to a worksheet (a good practice) with the Excel name of Colors, or Set Colors = wsColors.Range("B1:Q400", "S1:AI400") if you set the worksheet 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" wrote in message ... 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 -- Dave Peterson |
Declaring Objects
Thank you.
Bob Phillips wrote: Sharon, I don't think Dave was suggesting that you use MsgBox, he just included that so that you could verify it works (in testing) :-) -- HTH RP |
| All times are GMT +1. The time now is 03:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com