Change to existing macro

  • Thread starter Thread starter Jerry
  • Start date Start date
J

Jerry

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
 
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
 
Thanks for your assistance. When I run the code, I get the
following error message: Run-time error '424': Object
required
What causes this?
Thank you for your help.
 
Trying to edit your code in an email causes it.

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)
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
 
I suppose that this means that, unfortunately, there is no
easy solution with that code?
 
Other than I corrected the error and it runs fine, I don't know what you
mean.

What I meant was I edited your code in the email and forgot to remove the
..Copy on the end of one of your statements. Of course, this would be
easily detected when the error occurred when you tried to run it and easily
corrected. So the solution was easy - remove the residual code that should
have been removed previously.
 
Back
Top