Doing a calculation, then looping for several of the same calc

[jdfisher1]

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("B44").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

 

[Frank Haverkamp]

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

 

[jdfisher1]

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:4. The result (B4:4) should be pasted in sheet 3.
Does that make more sense

 

[Anders S]

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("B44").Value
rowNum = rowNum + 1
Next
End Sub
'*****

HTH,
Anders Silven

 

[jdfisher1]

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