Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a variable of type Range
Hi everyone:
I had posted a similar question a while ago, and I never got an answer that made sense to me. I am sure the experts did not understand my question. Basically, I am trying to create a variable of type Range, dump some cells into this variable, manipulate the contents (format, value, etc) of the variable without affecting the original cells where the data came from, and then dump my result in another area of the sheet. This way, I keep the original data intact, and show the manipulated data as well. I can come up with some work arounds on this, but I want to use a variable just like y=x y=2*y+1 z=y As you see in this example, y is a dummy variable and whatever I do to it, it will not affect x. That is exactly what I am trying to achieve, but with a range. Now, as I mentioned before, I need to manipulate values, formats, color, character formats, etc. I am not sure if this is at all possible. If it is, and someone has a code or explanation on how to do this, I appreciate your help. Bob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a variable of type Range
For the solution I have in mind... will the location you want to dump the
manipulated cells to be contiguous (whether the original cells were contiguous or not)? For example, if your original cells are A1:C3, F4:F9, G2:G7, then where to you want them when you dump them elsewhere on the sheet? -- Rick (MVP - Excel) "Bob" wrote in message ... Hi everyone: I had posted a similar question a while ago, and I never got an answer that made sense to me. I am sure the experts did not understand my question. Basically, I am trying to create a variable of type Range, dump some cells into this variable, manipulate the contents (format, value, etc) of the variable without affecting the original cells where the data came from, and then dump my result in another area of the sheet. This way, I keep the original data intact, and show the manipulated data as well. I can come up with some work arounds on this, but I want to use a variable just like y=x y=2*y+1 z=y As you see in this example, y is a dummy variable and whatever I do to it, it will not affect x. That is exactly what I am trying to achieve, but with a range. Now, as I mentioned before, I need to manipulate values, formats, color, character formats, etc. I am not sure if this is at all possible. If it is, and someone has a code or explanation on how to do this, I appreciate your help. Bob |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a variable of type Range
Hi Rick:
Thanks for your help. Yes, the data will be continuous, and may expand on more than one column. This is the same problem that we discussed on 9/28, and 9/30. At this point, I am not so much concerned about dumping the data, as storing my range in a variable, play with the variable, and not have the original data changed. Bob "Rick Rothstein" wrote in message ... For the solution I have in mind... will the location you want to dump the manipulated cells to be contiguous (whether the original cells were contiguous or not)? For example, if your original cells are A1:C3, F4:F9, G2:G7, then where to you want them when you dump them elsewhere on the sheet? -- Rick (MVP - Excel) "Bob" wrote in message ... Hi everyone: I had posted a similar question a while ago, and I never got an answer that made sense to me. I am sure the experts did not understand my question. Basically, I am trying to create a variable of type Range, dump some cells into this variable, manipulate the contents (format, value, etc) of the variable without affecting the original cells where the data came from, and then dump my result in another area of the sheet. This way, I keep the original data intact, and show the manipulated data as well. I can come up with some work arounds on this, but I want to use a variable just like y=x y=2*y+1 z=y As you see in this example, y is a dummy variable and whatever I do to it, it will not affect x. That is exactly what I am trying to achieve, but with a range. Now, as I mentioned before, I need to manipulate values, formats, color, character formats, etc. I am not sure if this is at all possible. If it is, and someone has a code or explanation on how to do this, I appreciate your help. Bob |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a variable of type Range
I don't think you can do what you asked, at least not directly. The only way
I can think of for you to do what you want is to copy the source cells to the destination location and then to either manipulate the location cells directly or assign them to your range variable and manipulate the cells by using the range variable you assigned them to. For example, something like this... Sub Test() Dim R As Range With Range("A1:F9") .Copy Range("X5") Set R = Range("X5").Resize(.Rows.Count, .Columns.Count) End With ' Now, manipulating R will manipulate the only ' the "dump to" range while leaving the original ' range intact. End Sub -- Rick (MVP - Excel) "Bob" wrote in message ... Hi Rick: Thanks for your help. Yes, the data will be continuous, and may expand on more than one column. This is the same problem that we discussed on 9/28, and 9/30. At this point, I am not so much concerned about dumping the data, as storing my range in a variable, play with the variable, and not have the original data changed. Bob "Rick Rothstein" wrote in message ... For the solution I have in mind... will the location you want to dump the manipulated cells to be contiguous (whether the original cells were contiguous or not)? For example, if your original cells are A1:C3, F4:F9, G2:G7, then where to you want them when you dump them elsewhere on the sheet? -- Rick (MVP - Excel) "Bob" wrote in message ... Hi everyone: I had posted a similar question a while ago, and I never got an answer that made sense to me. I am sure the experts did not understand my question. Basically, I am trying to create a variable of type Range, dump some cells into this variable, manipulate the contents (format, value, etc) of the variable without affecting the original cells where the data came from, and then dump my result in another area of the sheet. This way, I keep the original data intact, and show the manipulated data as well. I can come up with some work arounds on this, but I want to use a variable just like y=x y=2*y+1 z=y As you see in this example, y is a dummy variable and whatever I do to it, it will not affect x. That is exactly what I am trying to achieve, but with a range. Now, as I mentioned before, I need to manipulate values, formats, color, character formats, etc. I am not sure if this is at all possible. If it is, and someone has a code or explanation on how to do this, I appreciate your help. Bob |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a variable of type Range
Thanks Rick. That was what I thought and was afraid of. Let me look at it
during the next couple of days, and I will get back to you. Thanks for your help, and have a nice Sunday. Bob "Rick Rothstein" wrote in message ... I don't think you can do what you asked, at least not directly. The only way I can think of for you to do what you want is to copy the source cells to the destination location and then to either manipulate the location cells directly or assign them to your range variable and manipulate the cells by using the range variable you assigned them to. For example, something like this... Sub Test() Dim R As Range With Range("A1:F9") .Copy Range("X5") Set R = Range("X5").Resize(.Rows.Count, .Columns.Count) End With ' Now, manipulating R will manipulate the only ' the "dump to" range while leaving the original ' range intact. End Sub -- Rick (MVP - Excel) "Bob" wrote in message ... Hi Rick: Thanks for your help. Yes, the data will be continuous, and may expand on more than one column. This is the same problem that we discussed on 9/28, and 9/30. At this point, I am not so much concerned about dumping the data, as storing my range in a variable, play with the variable, and not have the original data changed. Bob "Rick Rothstein" wrote in message ... For the solution I have in mind... will the location you want to dump the manipulated cells to be contiguous (whether the original cells were contiguous or not)? For example, if your original cells are A1:C3, F4:F9, G2:G7, then where to you want them when you dump them elsewhere on the sheet? -- Rick (MVP - Excel) "Bob" wrote in message ... Hi everyone: I had posted a similar question a while ago, and I never got an answer that made sense to me. I am sure the experts did not understand my question. Basically, I am trying to create a variable of type Range, dump some cells into this variable, manipulate the contents (format, value, etc) of the variable without affecting the original cells where the data came from, and then dump my result in another area of the sheet. This way, I keep the original data intact, and show the manipulated data as well. I can come up with some work arounds on this, but I want to use a variable just like y=x y=2*y+1 z=y As you see in this example, y is a dummy variable and whatever I do to it, it will not affect x. That is exactly what I am trying to achieve, but with a range. Now, as I mentioned before, I need to manipulate values, formats, color, character formats, etc. I am not sure if this is at all possible. If it is, and someone has a code or explanation on how to do this, I appreciate your help. Bob |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a variable of type Range
Hi Bob,
You can think of a range variable as just a pointer to a block of Excel cells, with lots of associated properties and methods for manipulating the block of cells it is pointing to. It is not a copy of the cells, just a pointer to them. You can copy the values of the block of cells to a VBA variant and manipulate the values in the variant, then write them back to a different range. This is a lot faster than manipulating the cells directly one by one. You can also copy other properties such as formulae or number format (but only one property at a time) from the block of cells to VBA variants. As Rick has pointed out the simplest way to do what you want is to copy the range of cells somewhere else, then Set a Range variable that points to the copy and then manipulate that. Usually what I do is a combination of both approaches: - assign the Range.Value2 to a variant to create a copy of the values in an array - manipulate the values in the resulting variant array - assign the variant to a different range to write the values back - copy and pastespecial the formats from the original range to the copy - set a range variable to the copy - use the range variable to manipulate formats etc regards Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "Bob" wrote in message ... Hi everyone: I had posted a similar question a while ago, and I never got an answer that made sense to me. I am sure the experts did not understand my question. Basically, I am trying to create a variable of type Range, dump some cells into this variable, manipulate the contents (format, value, etc) of the variable without affecting the original cells where the data came from, and then dump my result in another area of the sheet. This way, I keep the original data intact, and show the manipulated data as well. I can come up with some work arounds on this, but I want to use a variable just like y=x y=2*y+1 z=y As you see in this example, y is a dummy variable and whatever I do to it, it will not affect x. That is exactly what I am trying to achieve, but with a range. Now, as I mentioned before, I need to manipulate values, formats, color, character formats, etc. I am not sure if this is at all possible. If it is, and someone has a code or explanation on how to do this, I appreciate your help. Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Variable Range | Excel Programming | |||
I need your help Dear Vba Guru's..."Creating range in a variable data.." | Excel Programming | |||
Type mismatch using rnge as Range with Type 8 Input Box | Excel Programming | |||
Macro Creating Variable and using variable in a SQL statement | Excel Programming | |||
Range type variable oddness | Excel Programming |