View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
 
Posts: 27,285
Default Change to existing macro

Sub Model()
Dim varr as variant
Dim i as long
Dim c as Range
Dim cell As Range
varr = Array("B30","B61","C12","R32","M13")
For Each cell In Worksheets("sheet1").Range("N6:N325")
i = lbound(varr)
for each c in cell.Resize(1, 5).Copy
worksheets("sheet2").range(varr(i)).Value = c.Value
i = i + 1
next
With Worksheets("sheet2")

.Range("B2").PasteSpecial Paste:=xlValues,
Transpose:=True
cell.Offset(0, -5).Value = .Range("B9").Value
cell.Offset(0, -4).Value = .Range("B10").Value
cell.Offset(0, -3).Value = .Range("B11").Value
cell.Offset(0, -2).Value = .Range("B12").Value

End With


Next

End Sub

--
Regards,
Tom Ogilvy

Jerry wrote in message
...
Hi,
I have a macro (see below) that the copies and pastes 5
arrays of assumptions, line by line, into the assumptions
input sheet of a model. It then records the output of the
model based on that particular set of assumptions. Now, I
would like to make the following change to the macro, but
I am not certain how:

The five arrays of assumptions are in sheet1 columns N
through Q. I would like be able to manipulate the macro,
so column N variables goes into a specific cell (Let's say
B31), column O goes into another specific cell independent
of column N (Let's say B61), and so forth. Right now the
macro simply copies and pastes a range of cells.

I hope this makes sense.

Thanks,
Jerry


Sub Model()

Dim cell As Range

For Each cell In Worksheets("sheet1").Range("N6:N325")
cell.Resize(1, 5).Copy

With Worksheets("sheet2")

.Range("B2").PasteSpecial Paste:=xlValues,
Transpose:=True
cell.Offset(0, -5).Value = .Range("B9").Value
cell.Offset(0, -4).Value = .Range("B10").Value
cell.Offset(0, -3).Value = .Range("B11").Value
cell.Offset(0, -2).Value = .Range("B12").Value

End With


Next

End Sub