Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Doing a calculation, then looping for several of the same calc
Hi -
I am new to VBA, so this may be easier than I think. I have spreadsheet that is set up into three sheets. Sheet one is calculation, sheet 2 has all the values that I need to be calculate individually, and sheet3 is where the answer from the calculation i pasted. The code I am using so far is this: Range("A1").Select Selection.Copy Sheets("Sheet1").Select Range("B4").Select ActiveSheet.Paste Range("B4:D4").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet3").Select Range("A1").Select ActiveSheet.Paste That does what I want it to for one selection (A1). However, I want t have this repeat for cells A1:A10 on Sheet 2 and have it pasted o A1:A10 on Sheet 3 respectively. Basically I want to perform an individual calculation that loops. Doe anyone know how to combine these? Thanks -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Doing a calculation, then looping for several of the same calc
Can I assume that Sheet 1 has an Input cell that you want the data fro
sheet 2 to populate, and an Output cell that you want pasted to shee 3? Or are you simply trying to make Sheet 3 be the product of th calculations done with sheets 1 & 2. Sheet3=Sheet1(+-/*%....)Sheet2 -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Doing a calculation, then looping for several of the same calc
Yes. Sheet 1 performs a calculation (separate from the macro)
Basically, sheet 1 is an accounting sheet that has pluses and minuse based on the input from A1 in Sheet2. The result from Sheet1 is foun in fields B4:D:4. The result (B4:D:4) should be pasted in sheet 3. Does that make more sense -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Doing a calculation, then looping for several of the same calc
jdfisher1,
I don't understand why you have to involve Sheet1 or use a macro at all. On Sheet3 you can reference Sheet2 like =Sheet2!A1 in cell A1, then fill down to A10 and do the calculations in columns B and C on Sheet3. However, if you must use a macro, you can try this: '***** Option Explicit Sub test7948() Dim srcSheet As Worksheet Dim calcSheet As Worksheet Dim destSheet As Worksheet Dim srcCell As Range Dim rowNum As Integer Set srcSheet = Sheets("Sheet2") Set calcSheet = Sheets("Sheet1") Set destSheet = Sheets("Sheet3") rowNum = 0 For Each srcCell In srcSheet.Range("A1:A10") calcSheet.Range("B4").Value = srcCell destSheet.Range("A1:C1").Offset(rowNum, 0) _ .Value = calcSheet.Range("B4:D4").Value rowNum = rowNum + 1 Next End Sub '***** HTH, Anders Silven "jdfisher1 " skrev i meddelandet ... Yes. Sheet 1 performs a calculation (separate from the macro). Basically, sheet 1 is an accounting sheet that has pluses and minuses based on the input from A1 in Sheet2. The result from Sheet1 is found in fields B4:D:4. The result (B4:D:4) should be pasted in sheet 3. Does that make more sense? --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Doing a calculation, then looping for several of the same calc
I am going to try that - the sheet is at work, so I will have to tr
tomorrow. I need a macro (instead of a standard =A1 type formula) because A1 i sheet 2 represents a client number which when input into Sheet calculates an account balance that is unique to A1...and A2, etc. Thanks for your suggestion - I will report back tomorrow -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calc = Manual & Do Not Calc b/4 SAVE | Excel Discussion (Misc queries) | |||
My calc key on Excel changes box to "Text" Box and doesn't calc ? | Charts and Charting in Excel | |||
Open CSV causes calculation in manual calc mode | Excel Discussion (Misc queries) | |||
auto calc on, but have to edit (f2) cells to force re-calc..help! | Excel Worksheet Functions | |||
looping a calculation until workbook closes | Excel Worksheet Functions |