Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Copy & paste in multiple areas using VBA
I'm trying to select a series of ranges to
1. paste a named formula I've created and then 2. remove the formula & leave the value What I have so far is shown below, but I'm sure there must be a better way. The problem seems to be that I can't use the copy function in a multiple range. I guess I can't paste xlValues to multiple ranges either, so I'm being forced to handle each range, one at a time, which seems rather cumbersome. Sub Macro1() Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134").Select Selection = "=ITNBudgetFormula" Range("G8:R45").Select Range("G8:R45").Copy Selection.PasteSpecial Paste:=xlValues Range("G50:R59").Select Range("G50:R59").Copy Selection.PasteSpecial Paste:=xlValues 'etc for the rest of the range End Sub |
#2
|
|||
|
|||
Rob -
One quick way around this is to give you collection of cells a name, say FormulaRange. Then use code like this instead of wht you've got Dim rng as Range Application.Screenupdating = false For Each rng in Range("FormulaRange") rng.select rng.formula = "=ITNBudgetFormula" rng.Copy rng.PasteSpecial xlvalues Next rng Application.Screenupdating = true Duke "Rob" wrote: I'm trying to select a series of ranges to 1. paste a named formula I've created and then 2. remove the formula & leave the value What I have so far is shown below, but I'm sure there must be a better way. The problem seems to be that I can't use the copy function in a multiple range. I guess I can't paste xlValues to multiple ranges either, so I'm being forced to handle each range, one at a time, which seems rather cumbersome. Sub Macro1() Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134").Select Selection = "=ITNBudgetFormula" Range("G8:R45").Select Range("G8:R45").Copy Selection.PasteSpecial Paste:=xlValues Range("G50:R59").Select Range("G50:R59").Copy Selection.PasteSpecial Paste:=xlValues 'etc for the rest of the range End Sub |
#4
|
|||
|
|||
Rob -
While my earlier post contained code for selecting each cell in the group, VBA code works much faster if you do not select cells. And the fact is that rarely do you need to select a cell to accomplish your goal. In this case, you may simply need to create your formula in VBA, using R1C1 references, assign the formula to the range of cells, then convert each cell to a value like so, which doesn't select ANY cells and work very, very fast With range("FormulaRange") .FormulaR1C1 = "=rc[-1]" For Each cc In range("FormulaRange") cc.Formula = cc.Value Next End With "Rob" wrote: I'm trying to select a series of ranges to 1. paste a named formula I've created and then 2. remove the formula & leave the value What I have so far is shown below, but I'm sure there must be a better way. The problem seems to be that I can't use the copy function in a multiple range. I guess I can't paste xlValues to multiple ranges either, so I'm being forced to handle each range, one at a time, which seems rather cumbersome. Sub Macro1() Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134").Select Selection = "=ITNBudgetFormula" Range("G8:R45").Select Range("G8:R45").Copy Selection.PasteSpecial Paste:=xlValues Range("G50:R59").Select Range("G50:R59").Copy Selection.PasteSpecial Paste:=xlValues 'etc for the rest of the range End Sub |
#6
|
|||
|
|||
|
#7
|
|||
|
|||
Thanks Don and Duke. And the winner is......
I really appreciate your input and will trial both to see what works best in my situation. Thanks for spending time to provide the best solution! Rob "Duke Carey" wrote in message ... True "Don Guillett" wrote: and my method should be even quicker -- Don Guillett SalesAid Software "Duke Carey" wrote in message ... Rob - While my earlier post contained code for selecting each cell in the group, VBA code works much faster if you do not select cells. And the fact is that rarely do you need to select a cell to accomplish your goal. In this case, you may simply need to create your formula in VBA, using R1C1 references, assign the formula to the range of cells, then convert each cell to a value like so, which doesn't select ANY cells and work very, very fast With range("FormulaRange") .FormulaR1C1 = "=rc[-1]" For Each cc In range("FormulaRange") cc.Formula = cc.Value Next End With "Rob" wrote: I'm trying to select a series of ranges to 1. paste a named formula I've created and then 2. remove the formula & leave the value What I have so far is shown below, but I'm sure there must be a better way. The problem seems to be that I can't use the copy function in a multiple range. I guess I can't paste xlValues to multiple ranges either, so I'm being forced to handle each range, one at a time, which seems rather cumbersome. Sub Macro1() Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134").Select Selection = "=ITNBudgetFormula" Range("G8:R45").Select Range("G8:R45").Copy Selection.PasteSpecial Paste:=xlValues Range("G50:R59").Select Range("G50:R59").Copy Selection.PasteSpecial Paste:=xlValues 'etc for the rest of the range End Sub |
#8
|
|||
|
|||
Back again!
I thought it was working OK but the formula I used from Don is doing some strange things I can't resolve. The modified formula I'm using is: Sub SetFormula() Set frng = Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134") Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134") = "=ITNBudgetFormula" With frng .Formula = .Value End With End Sub However, although most of it works throughout the ranges, there is one section that, as soon as the .Formula = .Value part executes, returns a #N/A error (within the range G63:R110, namely G101:R110). Furthermore, some of the data in other blocks of ranges show blank whereas all the others show 0 when all the data should be 0 because there is none yet. In fact, immediately prior to executing the .Formula = .Value, all the cells show 0. But as soon as .Formula = .Value executes these strange things happen. Any ideas? Rob "Rob" <NA wrote in message ... Thanks Don and Duke. And the winner is...... I really appreciate your input and will trial both to see what works best in my situation. Thanks for spending time to provide the best solution! Rob "Duke Carey" wrote in message ... True "Don Guillett" wrote: and my method should be even quicker -- Don Guillett SalesAid Software "Duke Carey" wrote in message ... Rob - While my earlier post contained code for selecting each cell in the group, VBA code works much faster if you do not select cells. And the fact is that rarely do you need to select a cell to accomplish your goal. In this case, you may simply need to create your formula in VBA, using R1C1 references, assign the formula to the range of cells, then convert each cell to a value like so, which doesn't select ANY cells and work very, very fast With range("FormulaRange") .FormulaR1C1 = "=rc[-1]" For Each cc In range("FormulaRange") cc.Formula = cc.Value Next End With "Rob" wrote: I'm trying to select a series of ranges to 1. paste a named formula I've created and then 2. remove the formula & leave the value What I have so far is shown below, but I'm sure there must be a better way. The problem seems to be that I can't use the copy function in a multiple range. I guess I can't paste xlValues to multiple ranges either, so I'm being forced to handle each range, one at a time, which seems rather cumbersome. Sub Macro1() Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134").Select Selection = "=ITNBudgetFormula" Range("G8:R45").Select Range("G8:R45").Copy Selection.PasteSpecial Paste:=xlValues Range("G50:R59").Select Range("G50:R59").Copy Selection.PasteSpecial Paste:=xlValues 'etc for the rest of the range End Sub |
#9
|
|||
|
|||
Since you did not mention what your formula is, I have NO idea what your
formula is. Test with what I sent and then modify. Sub SetFormula() Set frng = Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134") With frng 'No idea on this part ..formula = "ITNBudgetFormula" perhaps ?????? '.formula=a1*b2 'or whatever .Formula = .Value End With End Sub -- Don Guillett SalesAid Software "Rob" <NA wrote in message ... Back again! I thought it was working OK but the formula I used from Don is doing some strange things I can't resolve. The modified formula I'm using is: Sub SetFormula() Set frng = Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134") Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134") = "=ITNBudgetFormula" With frng .Formula = .Value End With End Sub However, although most of it works throughout the ranges, there is one section that, as soon as the .Formula = .Value part executes, returns a #N/A error (within the range G63:R110, namely G101:R110). Furthermore, some of the data in other blocks of ranges show blank whereas all the others show 0 when all the data should be 0 because there is none yet. In fact, immediately prior to executing the .Formula = .Value, all the cells show 0. But as soon as .Formula = .Value executes these strange things happen. Any ideas? Rob "Rob" <NA wrote in message ... Thanks Don and Duke. And the winner is...... I really appreciate your input and will trial both to see what works best in my situation. Thanks for spending time to provide the best solution! Rob "Duke Carey" wrote in message ... True "Don Guillett" wrote: and my method should be even quicker -- Don Guillett SalesAid Software "Duke Carey" wrote in message ... Rob - While my earlier post contained code for selecting each cell in the group, VBA code works much faster if you do not select cells. And the fact is that rarely do you need to select a cell to accomplish your goal. In this case, you may simply need to create your formula in VBA, using R1C1 references, assign the formula to the range of cells, then convert each cell to a value like so, which doesn't select ANY cells and work very, very fast With range("FormulaRange") .FormulaR1C1 = "=rc[-1]" For Each cc In range("FormulaRange") cc.Formula = cc.Value Next End With "Rob" wrote: I'm trying to select a series of ranges to 1. paste a named formula I've created and then 2. remove the formula & leave the value What I have so far is shown below, but I'm sure there must be a better way. The problem seems to be that I can't use the copy function in a multiple range. I guess I can't paste xlValues to multiple ranges either, so I'm being forced to handle each range, one at a time, which seems rather cumbersome. Sub Macro1() Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134").Select Selection = "=ITNBudgetFormula" Range("G8:R45").Select Range("G8:R45").Copy Selection.PasteSpecial Paste:=xlValues Range("G50:R59").Select Range("G50:R59").Copy Selection.PasteSpecial Paste:=xlValues 'etc for the rest of the range End Sub |
#10
|
|||
|
|||
Hi Don,
To run your code I've modified it as follows: Sub SetFormula1() Set frng = Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134") Sheet4.Unprotect With frng .Formula = "=ITNBudgetFormula" .Formula = .Value End With Sheet4.Protect End Sub The code puts the formula in OK and it shows the correct data BUT as soon as the line .Formula = .Value is executed all the data is returned to 0 except for the range G102:R110 which becomes #N/A AND any data in range G94:R101 becomes 0. I've tried 2 diff formulas to see if the formula could be the error but I don't think it is as the formulas return the correct values, and the same problem occurs with both. The formulas a 1. =IF($C63="",0,SUMPRODUCT(--(OldBudget!$C$8:$C$134=$C63),OldBudget!G$8:G$134)) 2. =IF($C63="",0,VLOOKUP($C63,OldBudget!$C$8:$R$134,A D$1,FALSE)) (The code you supplied copies the formula nicely to the full ranges as required, changing the cell references correctly and so, returning the correct results The problem happens only when .Formula = .Value is executed.) What I'm doing (using VBA), is copying a complete worksheet called Budget to another worksheet called OldBudget. I then enter or amend the data in column C on the Budget worksheet for certain reasons. Then.....and this is the process I'm having trouble with... I want the formula to find any data, now in the OldBudget worksheet that might be still applicable, depending on whether it matches the data in column C in the Budget worksheet. Rob "Don Guillett" wrote in message ... Since you did not mention what your formula is, I have NO idea what your formula is. Test with what I sent and then modify. Sub SetFormula() Set frng = Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134") With frng 'No idea on this part .formula = "ITNBudgetFormula" perhaps ?????? '.formula=a1*b2 'or whatever .Formula = .Value End With End Sub -- Don Guillett SalesAid Software "Rob" <NA wrote in message ... Back again! I thought it was working OK but the formula I used from Don is doing some strange things I can't resolve. The modified formula I'm using is: Sub SetFormula() Set frng = Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134") Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134") = "=ITNBudgetFormula" With frng .Formula = .Value End With End Sub However, although most of it works throughout the ranges, there is one section that, as soon as the .Formula = .Value part executes, returns a #N/A error (within the range G63:R110, namely G101:R110). Furthermore, some of the data in other blocks of ranges show blank whereas all the others show 0 when all the data should be 0 because there is none yet. In fact, immediately prior to executing the .Formula = .Value, all the cells show 0. But as soon as .Formula = .Value executes these strange things happen. Any ideas? Rob "Rob" <NA wrote in message ... Thanks Don and Duke. And the winner is...... I really appreciate your input and will trial both to see what works best in my situation. Thanks for spending time to provide the best solution! Rob "Duke Carey" wrote in message ... True "Don Guillett" wrote: and my method should be even quicker -- Don Guillett SalesAid Software "Duke Carey" wrote in message ... Rob - While my earlier post contained code for selecting each cell in the group, VBA code works much faster if you do not select cells. And the fact is that rarely do you need to select a cell to accomplish your goal. In this case, you may simply need to create your formula in VBA, using R1C1 references, assign the formula to the range of cells, then convert each cell to a value like so, which doesn't select ANY cells and work very, very fast With range("FormulaRange") .FormulaR1C1 = "=rc[-1]" For Each cc In range("FormulaRange") cc.Formula = cc.Value Next End With "Rob" wrote: I'm trying to select a series of ranges to 1. paste a named formula I've created and then 2. remove the formula & leave the value What I have so far is shown below, but I'm sure there must be a better way. The problem seems to be that I can't use the copy function in a multiple range. I guess I can't paste xlValues to multiple ranges either, so I'm being forced to handle each range, one at a time, which seems rather cumbersome. Sub Macro1() Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134").Select Selection = "=ITNBudgetFormula" Range("G8:R45").Select Range("G8:R45").Copy Selection.PasteSpecial Paste:=xlValues Range("G50:R59").Select Range("G50:R59").Copy Selection.PasteSpecial Paste:=xlValues 'etc for the rest of the range End Sub |
#11
|
|||
|
|||
Back again, Don.
I created a test workbook to eliminate any unforseen influences. In the process of doing that I discovered that I'd made a small error to the ranges which I fixed. Nevertheless, the problem still exists, except it's now a bit clearer to me. What's happening is, that when the .Formula=.Value executes, it pastes the result of the data held in cells G8:R46 to all the other ranges, namely, to G50:R59, and to G63:R110, and to G114:R122, and to G126:R134. ie. the values shown in G50:R59 become the values from G8:R46 the values shown in G63:R110 also become the values from G8:R46 and so on. It seems that because there are less cells in G8:R46 than in the range G63:R110, that the remaining cells in that range show #N/A when the .Formula = .Value executes. The latest version of the code (that creates the error) to extract the data from 1 sheet to the other is: Sub SetFormula1() 'To extract OldBudget data to Budget's current account numbers Sheet1.Activate Set frng = Range("G8:R46,G50:R59,G63:R110,G114:R122,G126:R134 ") frng.Select Selection.ClearContents With frng .Formula = "=ITNBudgetFormula" 'See my other post for the actual formula .Formula = .Value 'THIS DOES NOT SEEM TO WORK PROPERLY! End With Range("C6").Select End Sub Prior to running the above code the following code is run to copy the data from sheet 1 to sheet 2: Sub TransferBudget() 'To copy budget data to OldBudget sheet Sheet1.Range("C5:R975").Copy Sheet2.Select Range("C5").PasteSpecial Paste:=xlValues Application.CutCopyMode = False Sheet1.Activate End Sub I hope you can see what the problem is because every variation I try has no real effect and it's beyond my VBA knowledge to try something else. Rob "Don Guillett" wrote in message ... Since you did not mention what your formula is, I have NO idea what your formula is. Test with what I sent and then modify. Sub SetFormula() Set frng = Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134") With frng 'No idea on this part .formula = "ITNBudgetFormula" perhaps ?????? '.formula=a1*b2 'or whatever .Formula = .Value End With End Sub -- Don Guillett SalesAid Software "Rob" <NA wrote in message ... Back again! I thought it was working OK but the formula I used from Don is doing some strange things I can't resolve. The modified formula I'm using is: Sub SetFormula() Set frng = Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134") Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134") = "=ITNBudgetFormula" With frng .Formula = .Value End With End Sub However, although most of it works throughout the ranges, there is one section that, as soon as the .Formula = .Value part executes, returns a #N/A error (within the range G63:R110, namely G101:R110). Furthermore, some of the data in other blocks of ranges show blank whereas all the others show 0 when all the data should be 0 because there is none yet. In fact, immediately prior to executing the .Formula = .Value, all the cells show 0. But as soon as .Formula = .Value executes these strange things happen. Any ideas? Rob "Rob" <NA wrote in message ... Thanks Don and Duke. And the winner is...... I really appreciate your input and will trial both to see what works best in my situation. Thanks for spending time to provide the best solution! Rob "Duke Carey" wrote in message ... True "Don Guillett" wrote: and my method should be even quicker -- Don Guillett SalesAid Software "Duke Carey" wrote in message ... Rob - While my earlier post contained code for selecting each cell in the group, VBA code works much faster if you do not select cells. And the fact is that rarely do you need to select a cell to accomplish your goal. In this case, you may simply need to create your formula in VBA, using R1C1 references, assign the formula to the range of cells, then convert each cell to a value like so, which doesn't select ANY cells and work very, very fast With range("FormulaRange") .FormulaR1C1 = "=rc[-1]" For Each cc In range("FormulaRange") cc.Formula = cc.Value Next End With "Rob" wrote: I'm trying to select a series of ranges to 1. paste a named formula I've created and then 2. remove the formula & leave the value What I have so far is shown below, but I'm sure there must be a better way. The problem seems to be that I can't use the copy function in a multiple range. I guess I can't paste xlValues to multiple ranges either, so I'm being forced to handle each range, one at a time, which seems rather cumbersome. Sub Macro1() Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134").Select Selection = "=ITNBudgetFormula" Range("G8:R45").Select Range("G8:R45").Copy Selection.PasteSpecial Paste:=xlValues Range("G50:R59").Select Range("G50:R59").Copy Selection.PasteSpecial Paste:=xlValues 'etc for the rest of the range End Sub |
#12
|
|||
|
|||
As I said before, I have no idea what your formula is and don't care to
"search" for it. Tested with this. If you need more assistance post your formula or send me a SMALL wb. Sub formulaset() Set frng = Range("h2:h4,h7:h9") With frng .Formula = "=h1+d8" ' .Formula = .Value End With End Sub -- Don Guillett SalesAid Software "Rob" <NA wrote in message ... Back again, Don. I created a test workbook to eliminate any unforseen influences. In the process of doing that I discovered that I'd made a small error to the ranges which I fixed. Nevertheless, the problem still exists, except it's now a bit clearer to me. What's happening is, that when the .Formula=.Value executes, it pastes the result of the data held in cells G8:R46 to all the other ranges, namely, to G50:R59, and to G63:R110, and to G114:R122, and to G126:R134. ie. the values shown in G50:R59 become the values from G8:R46 the values shown in G63:R110 also become the values from G8:R46 and so on. It seems that because there are less cells in G8:R46 than in the range G63:R110, that the remaining cells in that range show #N/A when the ..Formula = .Value executes. The latest version of the code (that creates the error) to extract the data from 1 sheet to the other is: Sub SetFormula1() 'To extract OldBudget data to Budget's current account numbers Sheet1.Activate Set frng = Range("G8:R46,G50:R59,G63:R110,G114:R122,G126:R134 ") frng.Select Selection.ClearContents With frng .Formula = "=ITNBudgetFormula" 'See my other post for the actual formula .Formula = .Value 'THIS DOES NOT SEEM TO WORK PROPERLY! End With Range("C6").Select End Sub Prior to running the above code the following code is run to copy the data from sheet 1 to sheet 2: Sub TransferBudget() 'To copy budget data to OldBudget sheet Sheet1.Range("C5:R975").Copy Sheet2.Select Range("C5").PasteSpecial Paste:=xlValues Application.CutCopyMode = False Sheet1.Activate End Sub I hope you can see what the problem is because every variation I try has no real effect and it's beyond my VBA knowledge to try something else. Rob "Don Guillett" wrote in message ... Since you did not mention what your formula is, I have NO idea what your formula is. Test with what I sent and then modify. Sub SetFormula() Set frng = Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134") With frng 'No idea on this part .formula = "ITNBudgetFormula" perhaps ?????? '.formula=a1*b2 'or whatever .Formula = .Value End With End Sub -- Don Guillett SalesAid Software "Rob" <NA wrote in message ... Back again! I thought it was working OK but the formula I used from Don is doing some strange things I can't resolve. The modified formula I'm using is: Sub SetFormula() Set frng = Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134") Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134") = "=ITNBudgetFormula" With frng .Formula = .Value End With End Sub However, although most of it works throughout the ranges, there is one section that, as soon as the .Formula = .Value part executes, returns a #N/A error (within the range G63:R110, namely G101:R110). Furthermore, some of the data in other blocks of ranges show blank whereas all the others show 0 when all the data should be 0 because there is none yet. In fact, immediately prior to executing the .Formula = .Value, all the cells show 0. But as soon as .Formula = .Value executes these strange things happen. Any ideas? Rob "Rob" <NA wrote in message ... Thanks Don and Duke. And the winner is...... I really appreciate your input and will trial both to see what works best in my situation. Thanks for spending time to provide the best solution! Rob "Duke Carey" wrote in message ... True "Don Guillett" wrote: and my method should be even quicker -- Don Guillett SalesAid Software "Duke Carey" wrote in message ... Rob - While my earlier post contained code for selecting each cell in the group, VBA code works much faster if you do not select cells. And the fact is that rarely do you need to select a cell to accomplish your goal. In this case, you may simply need to create your formula in VBA, using R1C1 references, assign the formula to the range of cells, then convert each cell to a value like so, which doesn't select ANY cells and work very, very fast With range("FormulaRange") .FormulaR1C1 = "=rc[-1]" For Each cc In range("FormulaRange") cc.Formula = cc.Value Next End With "Rob" wrote: I'm trying to select a series of ranges to 1. paste a named formula I've created and then 2. remove the formula & leave the value What I have so far is shown below, but I'm sure there must be a better way. The problem seems to be that I can't use the copy function in a multiple range. I guess I can't paste xlValues to multiple ranges either, so I'm being forced to handle each range, one at a time, which seems rather cumbersome. Sub Macro1() Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134").Select Selection = "=ITNBudgetFormula" Range("G8:R45").Select Range("G8:R45").Copy Selection.PasteSpecial Paste:=xlValues Range("G50:R59").Select Range("G50:R59").Copy Selection.PasteSpecial Paste:=xlValues 'etc for the rest of the range End Sub |
#13
|
|||
|
|||
You did not supply any data on sheet2 for testing but this should work from
sheet1 without selections. Sub SetFormula() Set frng = Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134") With frng .ClearContents .Formula = [ITNBudgetFormula] .Formula = .Value End With Sheet2.UsedRange.ClearContents End Sub Also, on sheet1 place cursor at cell g8windowfreeze panessave -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... As I said before, I have no idea what your formula is and don't care to "search" for it. Tested with this. If you need more assistance post your formula or send me a SMALL wb. Sub formulaset() Set frng = Range("h2:h4,h7:h9") With frng .Formula = "=h1+d8" ' .Formula = .Value End With End Sub -- Don Guillett SalesAid Software "Rob" <NA wrote in message ... Back again, Don. I created a test workbook to eliminate any unforseen influences. In the process of doing that I discovered that I'd made a small error to the ranges which I fixed. Nevertheless, the problem still exists, except it's now a bit clearer to me. What's happening is, that when the .Formula=.Value executes, it pastes the result of the data held in cells G8:R46 to all the other ranges, namely, to G50:R59, and to G63:R110, and to G114:R122, and to G126:R134. ie. the values shown in G50:R59 become the values from G8:R46 the values shown in G63:R110 also become the values from G8:R46 and so on. It seems that because there are less cells in G8:R46 than in the range G63:R110, that the remaining cells in that range show #N/A when the .Formula = .Value executes. The latest version of the code (that creates the error) to extract the data from 1 sheet to the other is: Sub SetFormula1() 'To extract OldBudget data to Budget's current account numbers Sheet1.Activate Set frng = Range("G8:R46,G50:R59,G63:R110,G114:R122,G126:R134 ") frng.Select Selection.ClearContents With frng .Formula = "=ITNBudgetFormula" 'See my other post for the actual formula .Formula = .Value 'THIS DOES NOT SEEM TO WORK PROPERLY! End With Range("C6").Select End Sub Prior to running the above code the following code is run to copy the data from sheet 1 to sheet 2: Sub TransferBudget() 'To copy budget data to OldBudget sheet Sheet1.Range("C5:R975").Copy Sheet2.Select Range("C5").PasteSpecial Paste:=xlValues Application.CutCopyMode = False Sheet1.Activate End Sub I hope you can see what the problem is because every variation I try has no real effect and it's beyond my VBA knowledge to try something else. Rob "Don Guillett" wrote in message ... Since you did not mention what your formula is, I have NO idea what your formula is. Test with what I sent and then modify. Sub SetFormula() Set frng = Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134") With frng 'No idea on this part .formula = "ITNBudgetFormula" perhaps ?????? '.formula=a1*b2 'or whatever .Formula = .Value End With End Sub -- Don Guillett SalesAid Software "Rob" <NA wrote in message ... Back again! I thought it was working OK but the formula I used from Don is doing some strange things I can't resolve. The modified formula I'm using is: Sub SetFormula() Set frng = Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134") Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134") = "=ITNBudgetFormula" With frng .Formula = .Value End With End Sub However, although most of it works throughout the ranges, there is one section that, as soon as the .Formula = .Value part executes, returns a #N/A error (within the range G63:R110, namely G101:R110). Furthermore, some of the data in other blocks of ranges show blank whereas all the others show 0 when all the data should be 0 because there is none yet. In fact, immediately prior to executing the .Formula = .Value, all the cells show 0. But as soon as .Formula = .Value executes these strange things happen. Any ideas? Rob "Rob" <NA wrote in message ... Thanks Don and Duke. And the winner is...... I really appreciate your input and will trial both to see what works best in my situation. Thanks for spending time to provide the best solution! Rob "Duke Carey" wrote in message ... True "Don Guillett" wrote: and my method should be even quicker -- Don Guillett SalesAid Software "Duke Carey" wrote in message ... Rob - While my earlier post contained code for selecting each cell in the group, VBA code works much faster if you do not select cells. And the fact is that rarely do you need to select a cell to accomplish your goal. In this case, you may simply need to create your formula in VBA, using R1C1 references, assign the formula to the range of cells, then convert each cell to a value like so, which doesn't select ANY cells and work very, very fast With range("FormulaRange") .FormulaR1C1 = "=rc[-1]" For Each cc In range("FormulaRange") cc.Formula = cc.Value Next End With "Rob" wrote: I'm trying to select a series of ranges to 1. paste a named formula I've created and then 2. remove the formula & leave the value What I have so far is shown below, but I'm sure there must be a better way. The problem seems to be that I can't use the copy function in a multiple range. I guess I can't paste xlValues to multiple ranges either, so I'm being forced to handle each range, one at a time, which seems rather cumbersome. Sub Macro1() Range("G8:R45,G50:R59,G63:R110,G114:R121,G114:R122 ,G126:R134").Select Selection = "=ITNBudgetFormula" Range("G8:R45").Select Range("G8:R45").Copy Selection.PasteSpecial Paste:=xlValues Range("G50:R59").Select Range("G50:R59").Copy Selection.PasteSpecial Paste:=xlValues 'etc for the rest of the range End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
Can't Copy and Paste between Excel 2003 Workbooks | Excel Discussion (Misc queries) | |||
Copy and Paste and keep format the same | Excel Discussion (Misc queries) | |||
Copy & Paste | Excel Discussion (Misc queries) | |||
copy paste cell character limit | Excel Discussion (Misc queries) |