Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to subtract many cell areas (between sheets)
Hi,
I'm sure a lot of you have better options to suggest! I have several cell areas located on the same places in both sheets. To make it easy to understand, say:: Sheet1.Range(A1:E30) and Sheet1.Range(A43:E65) Sheet2.Range(A1:E30) and Sheet2.Range(A1:E65) I need the Ranges in Sheet2 to be subtracted with the same ranges in Sheet1. Between them are values that contains formulas, text and are empty etc. I started to code it using the PasteSpecial - Operation:=xlSubtract approach, but I guess I can't copy - paste/subract all areas at once because excel can't make operations on several areas, right? And activate, select, copy etc. every area and move between each sheet every time will be to much. Aren't there a "3D"- functions to use or something, I have heard of that. Does it work on this? It had to be VBA, not cell functions!! Any other ways? I don't want to loop through all rows, the action will take place every time the sheet opens (worksheet_activate ) and I got 200 rows. of information to loop through and that will take to much time. /Regards |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to subtract many cell areas (between sheets)
Sub Tester1()
set rng1 = Sheet2.Range("A1:E30") set rng2 = Sheet2.Range("A43:E65") set rng1a = Sheet1.Range("A1:E30") set rng2a = Sheet1.Range("A43:E65") rng1.Copy rng1a.PasteSpecial xlValues, xlSubtract rng2.Copy rng2a.PasteSpecial xlValues, xlSubstract End Sub -- Regards, Tom Ogilvy "Imbecill" wrote in message ... Hi, I'm sure a lot of you have better options to suggest! I have several cell areas located on the same places in both sheets. To make it easy to understand, say:: Sheet1.Range(A1:E30) and Sheet1.Range(A43:E65) Sheet2.Range(A1:E30) and Sheet2.Range(A1:E65) I need the Ranges in Sheet2 to be subtracted with the same ranges in Sheet1. Between them are values that contains formulas, text and are empty etc. I started to code it using the PasteSpecial - Operation:=xlSubtract approach, but I guess I can't copy - paste/subract all areas at once because excel can't make operations on several areas, right? And activate, select, copy etc. every area and move between each sheet every time will be to much. Aren't there a "3D"- functions to use or something, I have heard of that. Does it work on this? It had to be VBA, not cell functions!! Any other ways? I don't want to loop through all rows, the action will take place every time the sheet opens (worksheet_activate ) and I got 200 rows. of information to loop through and that will take to much time. /Regards |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to subtract many cell areas (between sheets)
Thank you Tom,
Once again you've helped me. I'm greatful for your help. /Regards "Tom Ogilvy" skrev i meddelandet ... Sub Tester1() set rng1 = Sheet2.Range("A1:E30") set rng2 = Sheet2.Range("A43:E65") set rng1a = Sheet1.Range("A1:E30") set rng2a = Sheet1.Range("A43:E65") rng1.Copy rng1a.PasteSpecial xlValues, xlSubtract rng2.Copy rng2a.PasteSpecial xlValues, xlSubstract End Sub -- Regards, Tom Ogilvy "Imbecill" wrote in message ... Hi, I'm sure a lot of you have better options to suggest! I have several cell areas located on the same places in both sheets. To make it easy to understand, say:: Sheet1.Range(A1:E30) and Sheet1.Range(A43:E65) Sheet2.Range(A1:E30) and Sheet2.Range(A1:E65) I need the Ranges in Sheet2 to be subtracted with the same ranges in Sheet1. Between them are values that contains formulas, text and are empty etc. I started to code it using the PasteSpecial - Operation:=xlSubtract approach, but I guess I can't copy - paste/subract all areas at once because excel can't make operations on several areas, right? And activate, select, copy etc. every area and move between each sheet every time will be to much. Aren't there a "3D"- functions to use or something, I have heard of that. Does it work on this? It had to be VBA, not cell functions!! Any other ways? I don't want to loop through all rows, the action will take place every time the sheet opens (worksheet_activate ) and I got 200 rows. of information to loop through and that will take to much time. /Regards |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem: How to subtract many cell areas (between sheets)
Tom,
I turned into a problem with the code. Since the cells subracted contains formulas, will a formula be created every time a subtraction occurs, and because the subtraction occurs when ws_activate event the number of formulas in the cells accumulate. How do I clear the content before I paste, so the cell product always will contain just "cell2-cell1?" I have tried "Application.rng1a.ClearContent " but it doesn't like the syntax. What is wrong and what can I do? The question is open, if any else is skilled to answer. /Regards? "Tom Ogilvy" skrev i meddelandet ... Sub Tester1() set rng1 = Sheet2.Range("A1:E30") set rng2 = Sheet2.Range("A43:E65") set rng1a = Sheet1.Range("A1:E30") set rng2a = Sheet1.Range("A43:E65") rng1.Copy rng1a.PasteSpecial xlValues, xlSubtract rng2.Copy rng2a.PasteSpecial xlValues, xlSubstract End Sub -- Regards, Tom Ogilvy "Imbecill" wrote in message ... Hi, I'm sure a lot of you have better options to suggest! I have several cell areas located on the same places in both sheets. To make it easy to understand, say:: Sheet1.Range(A1:E30) and Sheet1.Range(A43:E65) Sheet2.Range(A1:E30) and Sheet2.Range(A1:E65) I need the Ranges in Sheet2 to be subtracted with the same ranges in Sheet1. Between them are values that contains formulas, text and are empty etc. I started to code it using the PasteSpecial - Operation:=xlSubtract approach, but I guess I can't copy - paste/subract all areas at once because excel can't make operations on several areas, right? And activate, select, copy etc. every area and move between each sheet every time will be to much. Aren't there a "3D"- functions to use or something, I have heard of that. Does it work on this? It had to be VBA, not cell functions!! Any other ways? I don't want to loop through all rows, the action will take place every time the sheet opens (worksheet_activate ) and I got 200 rows. of information to loop through and that will take to much time. /Regards |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem: How to subtract many cell areas (between sheets)
rngA1.ClearContents
If sheet1 has formulas, then you are correct that the formula will progress each time. =formula =(formula)-3 =((formula)-3)-6 for example. But what else would you expect? How can you subtract a value from a value produced by a formula without doing something like that? You would have to be more explicit in what you want to happen in this situation. You could make all the formulas hard coded numbers before doing the pastespecial, then you would just get the net result (but you lose your formula). -- Regards, Tom Ogilvy wrote in message ... Tom, I turned into a problem with the code. Since the cells subracted contains formulas, will a formula be created every time a subtraction occurs, and because the subtraction occurs when ws_activate event the number of formulas in the cells accumulate. How do I clear the content before I paste, so the cell product always will contain just "cell2-cell1?" I have tried "Application.rng1a.ClearContent " but it doesn't like the syntax. What is wrong and what can I do? The question is open, if any else is skilled to answer. /Regards? "Tom Ogilvy" skrev i meddelandet ... Sub Tester1() set rng1 = Sheet2.Range("A1:E30") set rng2 = Sheet2.Range("A43:E65") set rng1a = Sheet1.Range("A1:E30") set rng2a = Sheet1.Range("A43:E65") rng1.Copy rng1a.PasteSpecial xlValues, xlSubtract rng2.Copy rng2a.PasteSpecial xlValues, xlSubstract End Sub -- Regards, Tom Ogilvy "Imbecill" wrote in message ... Hi, I'm sure a lot of you have better options to suggest! I have several cell areas located on the same places in both sheets. To make it easy to understand, say:: Sheet1.Range(A1:E30) and Sheet1.Range(A43:E65) Sheet2.Range(A1:E30) and Sheet2.Range(A1:E65) I need the Ranges in Sheet2 to be subtracted with the same ranges in Sheet1. Between them are values that contains formulas, text and are empty etc. I started to code it using the PasteSpecial - Operation:=xlSubtract approach, but I guess I can't copy - paste/subract all areas at once because excel can't make operations on several areas, right? And activate, select, copy etc. every area and move between each sheet every time will be to much. Aren't there a "3D"- functions to use or something, I have heard of that. Does it work on this? It had to be VBA, not cell functions!! Any other ways? I don't want to loop through all rows, the action will take place every time the sheet opens (worksheet_activate ) and I got 200 rows. of information to loop through and that will take to much time. /Regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why do all sheets in a workbook have gray areas on the ribbons? | Excel Discussion (Misc queries) | |||
Reducing numbers by one in defined areas over several sheets | Excel Worksheet Functions | |||
Unselect all selected areas in all sheets | Excel Discussion (Misc queries) | |||
How do I set print areas for multiple sheets simultaneously? | Excel Discussion (Misc queries) | |||
Printing non-adjacent areas on seperate sheets in one print job | Excel Programming |