Copying,Pasting,Looping, Confusing!!!

[kellymarie]

Hi...

Am struggling with some code... and let me know if I fail to make any sense!!

I am trying to create code that loops through a list of numbers one by one, pastes the number into a cell (always the same cell). That cell feeds into a formula. I then want the result of that formula pasted into a cell and then these results pasted as a list...

Help!!

Thanks

kelly

[Simon Lloyd]

Assuming your list of numbers is in column A, and the cell to paste to is B2, and that the formula cell is C2....and that you want your results in column A on sheet 2 then the code below will work!
[VBA]
Sub copy_n_paste()
Dim MyCell As Range
Dim Rng As Range
Dim TgtValue As Range
Set Rng = Sheets("Sheet1").Range("A2:A11")
Set TgtValue = Sheets("Sheet1").Range("C2")
For Each MyCell In Rng
MyCell.Copy Destination:=Sheets("Sheet1").Range("B2")
TgtValue.Copy
With Sheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0)
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End With
Next MyCell
End Sub
[/VBA]

[Norie]

Kelly

Why loop?

Couldn't you just use code to create all the required formulas for your list?

[kellymarie]

Thanks simon!! I'll let you know how I get on!!

The reason for the looping is that the formula is complex and has a lot of other variables. I want to repeat this with a list of about 2000 numbers (in fact its two lists of numbers that feed into the formula cell... but one step at a time!!)

Thanks for your response guys!

[Simon Lloyd]

For two lists use the slightly modified code below, highlight your lists and then give them a name "MyRange", look Herefor a "how to" on named ranges[vba]Sub copy_n_paste()
Dim Rng As Range
Dim TgtValue As Range
Application.ScreenUpdating = False
Set Rng = Sheets("Sheet1").Range("MyRange")
Set TgtValue = Sheets("Sheet1").Range("C2")
For Each MyCell In Rng
MyCell.Copy Destination:=Sheets("Sheet1").Range("B2")
TgtValue.Copy
With Sheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0)
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End With
Next MyCell
Application.ScreenUpdating = True
End Sub[/vba]

[kellymarie]

Great Thanks!!

I have found this code really useful!!

Thanks again!