Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
how to do these things in Excel with and without VBA?
Hi all,
I am learning Excel/VBA via using it in real day-to-day work. Two questions: 1. How to multiple a whole range of cells by 2 all together and all at once? (element-wise). Of course, I am looking for more flexibility, such as apply a function to the whole region of cells. I understand that it could be done for rectangular shapes; is there a way to do this by first select a bunch of cells and label the cells as one single region(non-rectangular and non-regular shaped), and then apply function to this region as a whole? 2. This time I have a function in XLL (treated as black box here, it usually takes a column of values, or a row of values, treating these cells internally as a "vector" in C++). I have verified that the function works properly. But now, what if I want to select cells which are scattered around and not arranged in one row or one column ... It's like a multiple selection, and I don't have criteria for VSelect -- the cells should be selected by hand, no uniform criteria for any automatic selection... Please help me! Thanks a lot! |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
how to do these things in Excel with and without VBA?
Use paste special, Check.. value, multiply..... Copy a 2, Select the cells
either by naming them or selecting them. Use paste special. "LunaMoon" wrote in message ... Hi all, I am learning Excel/VBA via using it in real day-to-day work. Two questions: 1. How to multiple a whole range of cells by 2 all together and all at once? (element-wise). Of course, I am looking for more flexibility, such as apply a function to the whole region of cells. I understand that it could be done for rectangular shapes; is there a way to do this by first select a bunch of cells and label the cells as one single region(non-rectangular and non-regular shaped), and then apply function to this region as a whole? 2. This time I have a function in XLL (treated as black box here, it usually takes a column of values, or a row of values, treating these cells internally as a "vector" in C++). I have verified that the function works properly. But now, what if I want to select cells which are scattered around and not arranged in one row or one column ... It's like a multiple selection, and I don't have criteria for VSelect -- the cells should be selected by hand, no uniform criteria for any automatic selection... Please help me! Thanks a lot! |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
how to do these things in Excel with and without VBA?
The cells you wish to multiply by 2 (or do anything else with, within
reason) do not have to be in a contiguous block of cells. Select a bunch of cells with numbers in them on a worksheet (make sure you use the Ctrl key to select them so that you have some contiguous cells and some isolated cells in the selection), then right-click the tab for that worksheet and select View Code (this will take you to the VBA editor and make sure the active code window is set for the worksheet where your selections are). Now, execute this statement in the Immediate window... Selection.Value = 2 * Selection.Value Now go back to the worksheet and you will each selected cell's value is twice as big as it was before. You, of course, do not have to use the Selection to do this; you can specify a discontiguous range directly in code and to the same operation... Range("A1:C4, J4, L6:L9").Value = 2 * Range("A1:C4, J4, L6:L9").Value And, of course, multiplication is not all you can do... Range("A1:C4, J4, L6:L9").Value = "XX" & Range("A1:C4, J4, L6:L9").Value where I have concatenated "XX" onto the front of whatever is in each cell in the discontiguous range "A1:C4, J4, L6:L9". Rick "LunaMoon" wrote in message ... Hi all, I am learning Excel/VBA via using it in real day-to-day work. Two questions: 1. How to multiple a whole range of cells by 2 all together and all at once? (element-wise). Of course, I am looking for more flexibility, such as apply a function to the whole region of cells. I understand that it could be done for rectangular shapes; is there a way to do this by first select a bunch of cells and label the cells as one single region(non-rectangular and non-regular shaped), and then apply function to this region as a whole? 2. This time I have a function in XLL (treated as black box here, it usually takes a column of values, or a row of values, treating these cells internally as a "vector" in C++). I have verified that the function works properly. But now, what if I want to select cells which are scattered around and not arranged in one row or one column ... It's like a multiple selection, and I don't have criteria for VSelect -- the cells should be selected by hand, no uniform criteria for any automatic selection... Please help me! Thanks a lot! |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
how to do these things in Excel with and without VBA?
I'm not sure about all that Rick.
2 * Selection.Value that would only work if the selection is a single cell, although the single result could be assigned to the value of a multi-area arr = Range("A1:C4, J4, L6:L9").Value In this 'arr' would only size to the first area, 4x3. Thereafter cannot do 2 * arr Generally discontiguous ranges need to be handled separately, eg For Each rngArea in multiRange.Areas Copy/paste(special) barely works with multiple areas, although it just might if the areas are same size or, say multiple areas of different heights but same width and in same columns. Regards, Peter T "Rick Rothstein (MVP - VB)" wrote in message ... The cells you wish to multiply by 2 (or do anything else with, within reason) do not have to be in a contiguous block of cells. Select a bunch of cells with numbers in them on a worksheet (make sure you use the Ctrl key to select them so that you have some contiguous cells and some isolated cells in the selection), then right-click the tab for that worksheet and select View Code (this will take you to the VBA editor and make sure the active code window is set for the worksheet where your selections are). Now, execute this statement in the Immediate window... Selection.Value = 2 * Selection.Value Now go back to the worksheet and you will each selected cell's value is twice as big as it was before. You, of course, do not have to use the Selection to do this; you can specify a discontiguous range directly in code and to the same operation... Range("A1:C4, J4, L6:L9").Value = 2 * Range("A1:C4, J4, L6:L9").Value And, of course, multiplication is not all you can do... Range("A1:C4, J4, L6:L9").Value = "XX" & Range("A1:C4, J4, L6:L9").Value where I have concatenated "XX" onto the front of whatever is in each cell in the discontiguous range "A1:C4, J4, L6:L9". Rick "LunaMoon" wrote in message ... Hi all, I am learning Excel/VBA via using it in real day-to-day work. Two questions: 1. How to multiple a whole range of cells by 2 all together and all at once? (element-wise). Of course, I am looking for more flexibility, such as apply a function to the whole region of cells. I understand that it could be done for rectangular shapes; is there a way to do this by first select a bunch of cells and label the cells as one single region(non-rectangular and non-regular shaped), and then apply function to this region as a whole? 2. This time I have a function in XLL (treated as black box here, it usually takes a column of values, or a row of values, treating these cells internally as a "vector" in C++). I have verified that the function works properly. But now, what if I want to select cells which are scattered around and not arranged in one row or one column ... It's like a multiple selection, and I don't have criteria for VSelect -- the cells should be selected by hand, no uniform criteria for any automatic selection... Please help me! Thanks a lot! |
#5
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
how to do these things in Excel with and without VBA?
I'm not sure what to tell you, but I tried what I posted before posting it
(and again just now to confirm it) and what I posted works as I described it on my system, which the Help/About menu item says is Microsoft Office Excel 2003 (11.8211.8202) SP3. What I did is layout a large block of cells with 1's in them and then (for the selection method) I randomly selected discontiguous blocks and single cells with the Control Key down to make a discontiguous overall selection. Next, I right-clicked the worksheet tab and picked View Code in order to make the worksheet the active code window in the VBA editor. And then I typed... Selection.Value = 2 * Selection.Value in the Immediate Window and hit the Enter Key. All the values in the discontiguous Selection were multiplied by 2. Next I typed Selection.Value = "AB" & Selection.Value & "CD" into the Immediate Window and hit the Enter Key. All the values in the discontiguous Selection that were previously multiplied by 2 now had "AB" concatenated on the front of them and "CD" onto their backs. Then I repeated the exact same steps, but used a text string value as the argument for the Range collection object and the same things happened for the specified discontiguous cells that the text string represented. Are you saying none of the above happens for you when you try what I have outlined? Rick "Peter T" <peter_t@discussions wrote in message ... I'm not sure about all that Rick. 2 * Selection.Value that would only work if the selection is a single cell, although the single result could be assigned to the value of a multi-area arr = Range("A1:C4, J4, L6:L9").Value In this 'arr' would only size to the first area, 4x3. Thereafter cannot do 2 * arr Generally discontiguous ranges need to be handled separately, eg For Each rngArea in multiRange.Areas Copy/paste(special) barely works with multiple areas, although it just might if the areas are same size or, say multiple areas of different heights but same width and in same columns. Regards, Peter T "Rick Rothstein (MVP - VB)" wrote in message ... The cells you wish to multiply by 2 (or do anything else with, within reason) do not have to be in a contiguous block of cells. Select a bunch of cells with numbers in them on a worksheet (make sure you use the Ctrl key to select them so that you have some contiguous cells and some isolated cells in the selection), then right-click the tab for that worksheet and select View Code (this will take you to the VBA editor and make sure the active code window is set for the worksheet where your selections are). Now, execute this statement in the Immediate window... Selection.Value = 2 * Selection.Value Now go back to the worksheet and you will each selected cell's value is twice as big as it was before. You, of course, do not have to use the Selection to do this; you can specify a discontiguous range directly in code and to the same operation... Range("A1:C4, J4, L6:L9").Value = 2 * Range("A1:C4, J4, L6:L9").Value And, of course, multiplication is not all you can do... Range("A1:C4, J4, L6:L9").Value = "XX" & Range("A1:C4, J4, L6:L9").Value where I have concatenated "XX" onto the front of whatever is in each cell in the discontiguous range "A1:C4, J4, L6:L9". Rick "LunaMoon" wrote in message ... Hi all, I am learning Excel/VBA via using it in real day-to-day work. Two questions: 1. How to multiple a whole range of cells by 2 all together and all at once? (element-wise). Of course, I am looking for more flexibility, such as apply a function to the whole region of cells. I understand that it could be done for rectangular shapes; is there a way to do this by first select a bunch of cells and label the cells as one single region(non-rectangular and non-regular shaped), and then apply function to this region as a whole? 2. This time I have a function in XLL (treated as black box here, it usually takes a column of values, or a row of values, treating these cells internally as a "vector" in C++). I have verified that the function works properly. But now, what if I want to select cells which are scattered around and not arranged in one row or one column ... It's like a multiple selection, and I don't have criteria for VSelect -- the cells should be selected by hand, no uniform criteria for any automatic selection... Please help me! Thanks a lot! |
#6
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
how to do these things in Excel with and without VBA?
Okay, this is strange. Everything I posted, which worked fine as I posted
it, has now stopped working! All I did was unselect the selection I had, went to Help/About again, came back and made a different discontiguous selection and now Selection.Value = 2 * Selection.Value generates a "Type Mismatch" error. Same for Range with the discontiguous text string reference. I have no answer as to why, but it seems you were correct.... in the general case, you cannot rely upon what posted (which **was** working for me just a short time ago). Weird! Rick "Rick Rothstein (MVP - VB)" wrote in message ... I'm not sure what to tell you, but I tried what I posted before posting it (and again just now to confirm it) and what I posted works as I described it on my system, which the Help/About menu item says is Microsoft Office Excel 2003 (11.8211.8202) SP3. What I did is layout a large block of cells with 1's in them and then (for the selection method) I randomly selected discontiguous blocks and single cells with the Control Key down to make a discontiguous overall selection. Next, I right-clicked the worksheet tab and picked View Code in order to make the worksheet the active code window in the VBA editor. And then I typed... Selection.Value = 2 * Selection.Value in the Immediate Window and hit the Enter Key. All the values in the discontiguous Selection were multiplied by 2. Next I typed Selection.Value = "AB" & Selection.Value & "CD" into the Immediate Window and hit the Enter Key. All the values in the discontiguous Selection that were previously multiplied by 2 now had "AB" concatenated on the front of them and "CD" onto their backs. Then I repeated the exact same steps, but used a text string value as the argument for the Range collection object and the same things happened for the specified discontiguous cells that the text string represented. Are you saying none of the above happens for you when you try what I have outlined? Rick "Peter T" <peter_t@discussions wrote in message ... I'm not sure about all that Rick. 2 * Selection.Value that would only work if the selection is a single cell, although the single result could be assigned to the value of a multi-area arr = Range("A1:C4, J4, L6:L9").Value In this 'arr' would only size to the first area, 4x3. Thereafter cannot do 2 * arr Generally discontiguous ranges need to be handled separately, eg For Each rngArea in multiRange.Areas Copy/paste(special) barely works with multiple areas, although it just might if the areas are same size or, say multiple areas of different heights but same width and in same columns. Regards, Peter T "Rick Rothstein (MVP - VB)" wrote in message ... The cells you wish to multiply by 2 (or do anything else with, within reason) do not have to be in a contiguous block of cells. Select a bunch of cells with numbers in them on a worksheet (make sure you use the Ctrl key to select them so that you have some contiguous cells and some isolated cells in the selection), then right-click the tab for that worksheet and select View Code (this will take you to the VBA editor and make sure the active code window is set for the worksheet where your selections are). Now, execute this statement in the Immediate window... Selection.Value = 2 * Selection.Value Now go back to the worksheet and you will each selected cell's value is twice as big as it was before. You, of course, do not have to use the Selection to do this; you can specify a discontiguous range directly in code and to the same operation... Range("A1:C4, J4, L6:L9").Value = 2 * Range("A1:C4, J4, L6:L9").Value And, of course, multiplication is not all you can do... Range("A1:C4, J4, L6:L9").Value = "XX" & Range("A1:C4, J4, L6:L9").Value where I have concatenated "XX" onto the front of whatever is in each cell in the discontiguous range "A1:C4, J4, L6:L9". Rick "LunaMoon" wrote in message ... Hi all, I am learning Excel/VBA via using it in real day-to-day work. Two questions: 1. How to multiple a whole range of cells by 2 all together and all at once? (element-wise). Of course, I am looking for more flexibility, such as apply a function to the whole region of cells. I understand that it could be done for rectangular shapes; is there a way to do this by first select a bunch of cells and label the cells as one single region(non-rectangular and non-regular shaped), and then apply function to this region as a whole? 2. This time I have a function in XLL (treated as black box here, it usually takes a column of values, or a row of values, treating these cells internally as a "vector" in C++). I have verified that the function works properly. But now, what if I want to select cells which are scattered around and not arranged in one row or one column ... It's like a multiple selection, and I don't have criteria for VSelect -- the cells should be selected by hand, no uniform criteria for any automatic selection... Please help me! Thanks a lot! |
#7
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
how to do these things in Excel with and without VBA?
One more follow up. I closed down Excel completely, restarted it again and
everything is working as I posted it again. Hmm! It seems to work on a newly started session of Excel and then, as things happen during the session (although I am not sure what those things are<g), it ceases to work any more until a new session of Excel is started again. Rick "Rick Rothstein (MVP - VB)" wrote in message ... Okay, this is strange. Everything I posted, which worked fine as I posted it, has now stopped working! All I did was unselect the selection I had, went to Help/About again, came back and made a different discontiguous selection and now Selection.Value = 2 * Selection.Value generates a "Type Mismatch" error. Same for Range with the discontiguous text string reference. I have no answer as to why, but it seems you were correct.... in the general case, you cannot rely upon what posted (which **was** working for me just a short time ago). Weird! Rick "Rick Rothstein (MVP - VB)" wrote in message ... I'm not sure what to tell you, but I tried what I posted before posting it (and again just now to confirm it) and what I posted works as I described it on my system, which the Help/About menu item says is Microsoft Office Excel 2003 (11.8211.8202) SP3. What I did is layout a large block of cells with 1's in them and then (for the selection method) I randomly selected discontiguous blocks and single cells with the Control Key down to make a discontiguous overall selection. Next, I right-clicked the worksheet tab and picked View Code in order to make the worksheet the active code window in the VBA editor. And then I typed... Selection.Value = 2 * Selection.Value in the Immediate Window and hit the Enter Key. All the values in the discontiguous Selection were multiplied by 2. Next I typed Selection.Value = "AB" & Selection.Value & "CD" into the Immediate Window and hit the Enter Key. All the values in the discontiguous Selection that were previously multiplied by 2 now had "AB" concatenated on the front of them and "CD" onto their backs. Then I repeated the exact same steps, but used a text string value as the argument for the Range collection object and the same things happened for the specified discontiguous cells that the text string represented. Are you saying none of the above happens for you when you try what I have outlined? Rick "Peter T" <peter_t@discussions wrote in message ... I'm not sure about all that Rick. 2 * Selection.Value that would only work if the selection is a single cell, although the single result could be assigned to the value of a multi-area arr = Range("A1:C4, J4, L6:L9").Value In this 'arr' would only size to the first area, 4x3. Thereafter cannot do 2 * arr Generally discontiguous ranges need to be handled separately, eg For Each rngArea in multiRange.Areas Copy/paste(special) barely works with multiple areas, although it just might if the areas are same size or, say multiple areas of different heights but same width and in same columns. Regards, Peter T "Rick Rothstein (MVP - VB)" wrote in message ... The cells you wish to multiply by 2 (or do anything else with, within reason) do not have to be in a contiguous block of cells. Select a bunch of cells with numbers in them on a worksheet (make sure you use the Ctrl key to select them so that you have some contiguous cells and some isolated cells in the selection), then right-click the tab for that worksheet and select View Code (this will take you to the VBA editor and make sure the active code window is set for the worksheet where your selections are). Now, execute this statement in the Immediate window... Selection.Value = 2 * Selection.Value Now go back to the worksheet and you will each selected cell's value is twice as big as it was before. You, of course, do not have to use the Selection to do this; you can specify a discontiguous range directly in code and to the same operation... Range("A1:C4, J4, L6:L9").Value = 2 * Range("A1:C4, J4, L6:L9").Value And, of course, multiplication is not all you can do... Range("A1:C4, J4, L6:L9").Value = "XX" & Range("A1:C4, J4, L6:L9").Value where I have concatenated "XX" onto the front of whatever is in each cell in the discontiguous range "A1:C4, J4, L6:L9". Rick "LunaMoon" wrote in message ... Hi all, I am learning Excel/VBA via using it in real day-to-day work. Two questions: 1. How to multiple a whole range of cells by 2 all together and all at once? (element-wise). Of course, I am looking for more flexibility, such as apply a function to the whole region of cells. I understand that it could be done for rectangular shapes; is there a way to do this by first select a bunch of cells and label the cells as one single region(non-rectangular and non-regular shaped), and then apply function to this region as a whole? 2. This time I have a function in XLL (treated as black box here, it usually takes a column of values, or a row of values, treating these cells internally as a "vector" in C++). I have verified that the function works properly. But now, what if I want to select cells which are scattered around and not arranged in one row or one column ... It's like a multiple selection, and I don't have criteria for VSelect -- the cells should be selected by hand, no uniform criteria for any automatic selection... Please help me! Thanks a lot! |
#8
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
how to do these things in Excel with and without VBA?
I was about to send a reply to your previous message, which I'll paste here
instead - "Strange and weird indeed as I have no doubt you reported what you saw. I can only speculate there was something accidentally particular about the multi-area selection you had." Even if that had not produced the mismatch error, with all cells filled with one's the result would have been misleading. At best, and providing the first area was the largest in both dimensions, an array of the first area only would have been filled into all areas, albeit reduced to respective sizes, thereby giving the impression that each area of the source had been processed. Maybe the following will illustrate what I mean - Sub foo() Dim r&, c&, rng As Range [a1:h15].Clear For r = 1 To 9 Cells(r, 1) = r For c = 1 To 5 Cells(r, c + 1) = r * 10 ^ c Next Next Set rng = Range("A1:C3,B5:C9,D6:F7,E8:E11,B13:G13,F1") rng.Select Stop ' have a look, press F5 to continue rng.Value = rng.Value End Sub " ======================== Now to your latest. This is more than weird it's darn right freaky ! I could not re-create similar in a new session and would not expect to. Shame, could be quite handy if there was some secret method to expose... Regards, Peter T "Rick Rothstein (MVP - VB)" wrote in message ... One more follow up. I closed down Excel completely, restarted it again and everything is working as I posted it again. Hmm! It seems to work on a newly started session of Excel and then, as things happen during the session (although I am not sure what those things are<g), it ceases to work any more until a new session of Excel is started again. Rick "Rick Rothstein (MVP - VB)" wrote in message ... Okay, this is strange. Everything I posted, which worked fine as I posted it, has now stopped working! All I did was unselect the selection I had, went to Help/About again, came back and made a different discontiguous selection and now Selection.Value = 2 * Selection.Value generates a "Type Mismatch" error. Same for Range with the discontiguous text string reference. I have no answer as to why, but it seems you were correct.... in the general case, you cannot rely upon what posted (which **was** working for me just a short time ago). Weird! Rick "Rick Rothstein (MVP - VB)" wrote in message ... I'm not sure what to tell you, but I tried what I posted before posting it (and again just now to confirm it) and what I posted works as I described it on my system, which the Help/About menu item says is Microsoft Office Excel 2003 (11.8211.8202) SP3. What I did is layout a large block of cells with 1's in them and then (for the selection method) I randomly selected discontiguous blocks and single cells with the Control Key down to make a discontiguous overall selection. Next, I right-clicked the worksheet tab and picked View Code in order to make the worksheet the active code window in the VBA editor. And then I typed... Selection.Value = 2 * Selection.Value in the Immediate Window and hit the Enter Key. All the values in the discontiguous Selection were multiplied by 2. Next I typed Selection.Value = "AB" & Selection.Value & "CD" into the Immediate Window and hit the Enter Key. All the values in the discontiguous Selection that were previously multiplied by 2 now had "AB" concatenated on the front of them and "CD" onto their backs. Then I repeated the exact same steps, but used a text string value as the argument for the Range collection object and the same things happened for the specified discontiguous cells that the text string represented. Are you saying none of the above happens for you when you try what I have outlined? Rick "Peter T" <peter_t@discussions wrote in message ... I'm not sure about all that Rick. 2 * Selection.Value that would only work if the selection is a single cell, although the single result could be assigned to the value of a multi-area arr = Range("A1:C4, J4, L6:L9").Value In this 'arr' would only size to the first area, 4x3. Thereafter cannot do 2 * arr Generally discontiguous ranges need to be handled separately, eg For Each rngArea in multiRange.Areas Copy/paste(special) barely works with multiple areas, although it just might if the areas are same size or, say multiple areas of different heights but same width and in same columns. Regards, Peter T "Rick Rothstein (MVP - VB)" wrote in message ... The cells you wish to multiply by 2 (or do anything else with, within reason) do not have to be in a contiguous block of cells. Select a bunch of cells with numbers in them on a worksheet (make sure you use the Ctrl key to select them so that you have some contiguous cells and some isolated cells in the selection), then right-click the tab for that worksheet and select View Code (this will take you to the VBA editor and make sure the active code window is set for the worksheet where your selections are). Now, execute this statement in the Immediate window... Selection.Value = 2 * Selection.Value Now go back to the worksheet and you will each selected cell's value is twice as big as it was before. You, of course, do not have to use the Selection to do this; you can specify a discontiguous range directly in code and to the same operation... Range("A1:C4, J4, L6:L9").Value = 2 * Range("A1:C4, J4, L6:L9").Value And, of course, multiplication is not all you can do... Range("A1:C4, J4, L6:L9").Value = "XX" & Range("A1:C4, J4, L6:L9").Value where I have concatenated "XX" onto the front of whatever is in each cell in the discontiguous range "A1:C4, J4, L6:L9". Rick "LunaMoon" wrote in message ... Hi all, I am learning Excel/VBA via using it in real day-to-day work. Two questions: 1. How to multiple a whole range of cells by 2 all together and all at once? (element-wise). Of course, I am looking for more flexibility, such as apply a function to the whole region of cells. I understand that it could be done for rectangular shapes; is there a way to do this by first select a bunch of cells and label the cells as one single region(non-rectangular and non-regular shaped), and then apply function to this region as a whole? 2. This time I have a function in XLL (treated as black box here, it usually takes a column of values, or a row of values, treating these cells internally as a "vector" in C++). I have verified that the function works properly. But now, what if I want to select cells which are scattered around and not arranged in one row or one column ... It's like a multiple selection, and I don't have criteria for VSelect -- the cells should be selected by hand, no uniform criteria for any automatic selection... Please help me! Thanks a lot! |
#9
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
how to do these things in Excel with and without VBA?
"Strange and weird indeed as I have no doubt you reported what you saw. I
can only speculate there was something accidentally particular about the multi-area selection you had." I've tried it with various sized selections and, for a new session of Excel, I can make it work every time. As I said though, I have no idea what combination of worksheet events are the trigger, but it seems to always fail after doing things with the worksheet. Rick |
#10
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
how to do these things in Excel with and without VBA?
"Rick Rothstein (MVP - VB)" wrote in
I've tried it with various sized selections and, for a new session of Excel, I can make it work every time. As I said though, I have no idea what combination of worksheet events are the trigger, but it seems to always fail after doing things with the worksheet. Does it also work with unique values in cells rather than all one's (eg as populated by that macro I posted earlier). Not only not-error but (say) multiply values in each area by 2 Selection = 2 * Selection ' a multi area discontiguous range Is there anyone else who can re-create what Rick has described in a new session, even with one's throughout ? Regards, Peter T |
#11
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
how to do these things in Excel with and without VBA?
LOL... I don't get it! I tried to use varying amounts in the cells (using
series fills) and it didn't work. NOW, I can't even get the method that was working consistently to work anymore... no matter how many time I start a new session and repeat the exact same process that worked before, I can no longer get it to work any more... not even once. Rick "Peter T" <peter_t@discussions wrote in message ... "Rick Rothstein (MVP - VB)" wrote in I've tried it with various sized selections and, for a new session of Excel, I can make it work every time. As I said though, I have no idea what combination of worksheet events are the trigger, but it seems to always fail after doing things with the worksheet. Does it also work with unique values in cells rather than all one's (eg as populated by that macro I posted earlier). Not only not-error but (say) multiply values in each area by 2 Selection = 2 * Selection ' a multi area discontiguous range Is there anyone else who can re-create what Rick has described in a new session, even with one's throughout ? Regards, Peter T |
#12
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
how to do these things in Excel with and without VBA?
Oh well, one of life's little mysteries then.
FWIW, anything along the following lines will always work, even where the selection is a multi area range Selection.Value = 2 * Selection(1).Value Regards, Peter T "Rick Rothstein (MVP - VB)" wrote in LOL... I don't get it! I tried to use varying amounts in the cells (using series fills) and it didn't work. NOW, I can't even get the method that was working consistently to work anymore... no matter how many time I start a new session and repeat the exact same process that worked before, I can no longer get it to work any more... not even once. Rick "Peter T" <peter_t@discussions wrote in message ... "Rick Rothstein (MVP - VB)" wrote in I've tried it with various sized selections and, for a new session of Excel, I can make it work every time. As I said though, I have no idea what combination of worksheet events are the trigger, but it seems to always fail after doing things with the worksheet. Does it also work with unique values in cells rather than all one's (eg as populated by that macro I posted earlier). Not only not-error but (say) multiply values in each area by 2 Selection = 2 * Selection ' a multi area discontiguous range Is there anyone else who can re-create what Rick has described in a new session, even with one's throughout ? Regards, Peter T |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste 2 different things from Excel to other programs | Excel Worksheet Functions | |||
Deleting things automatically in Excel | Excel Discussion (Misc queries) | |||
Strange things happening in Excel!!!!! | Excel Discussion (Misc queries) | |||
Can Excel do things lilke Quicken | Excel Discussion (Misc queries) | |||
Can Excel do things like Quicken | Excel Discussion (Misc queries) |