| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change to existing macro
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change to existing macro
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. -----Original Message----- 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 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change to existing macro
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 -- Regards, Tom Ogilvy Jerry wrote in message ... 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. -----Original Message----- 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 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change to existing macro
I suppose that this means that, unfortunately, there is no
easy solution with that code? -----Original Message----- 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 -- Regards, Tom Ogilvy Jerry wrote in message ... 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. -----Original Message----- 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 . . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change to existing macro
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. -- Regards, Tom Ogilvy wrote in message ... I suppose that this means that, unfortunately, there is no easy solution with that code? -----Original Message----- 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 -- Regards, Tom Ogilvy Jerry wrote in message ... 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. -----Original Message----- 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 . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do I change existing colours on a sheet? | Excel Discussion (Misc queries) | |||
| how to change existing column to capital letters | Excel Discussion (Misc queries) | |||
| How to change an existing query | Excel Discussion (Misc queries) | |||
| how to change font for all existing comments at once? | Excel Worksheet Functions | |||
| How do I change from all caps to regular for existing data? | Excel Discussion (Misc queries) | |||
![Jerry[_14_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
