I am trying to create a macro which does an iterative process


I have a spreadsheet which performs a complicated series of calculations
based on a generic formula.

the 2 inputs that I have i would like to be changed are based in 2 cells and
I would like the value of the input (at the touch of a buttton) changed in an
iterative process ie a + 2; a+4 .... a+40, date+1day;
date+2day...date+20days. and then the result foudn in cell (x,y) I would
like paste into a matrix. a x b.

I presume that there is some standard coding which contains a looop function
which can perform this task,
Can anyone help?

Sub Iterate()
dim i as Long, j as long
for i = 1 to 20
for j = 1 to 20
' increment number
Range("A1").Value = Range("A1").Value + 2
' increment date
Range("B1").Value = Range("B1").Value + 1
' write results
Range("M1").Offset(i,j).Value = Range("C1").Value
Next j
Next i
End Sub

Adjust locations and loop limits to suit.

--
Regards,
Tom Ogilvy



"Cammy" wrote:

I am trying to create a macro which does an iterative process


I have a spreadsheet which performs a complicated series of calculations
based on a generic formula.

the 2 inputs that I have i would like to be changed are based in 2 cells and
I would like the value of the input (at the touch of a buttton) changed in an
iterative process ie a + 2; a+4 .... a+40, date+1day;
date+2day...date+20days. and then the result foudn in cell (x,y) I would
like paste into a matrix. a x b.

I presume that there is some standard coding which contains a looop function
which can perform this task,
Can anyone help?

sorry I don't get how this is tranferring the outut value (I need a cut and
pastesepcial as a value) created from the formula with inputs a, b into an
actual matrix?
for example
cell a1 = input a
cell a2 = input b
cell a3 = outputy
with an output matrix created:
a-4 a-2 a a+2 a+4
b
b+1
b+2
b+3

"Tom Ogilvy" wrote:

Sub Iterate()
dim i as Long, j as long
for i = 1 to 20
for j = 1 to 20
' increment number
Range("A1").Value = Range("A1").Value + 2
' increment date
Range("B1").Value = Range("B1").Value + 1
' write results
Range("M1").Offset(i,j).Value = Range("C1").Value
Next j
Next i
End Sub

Adjust locations and loop limits to suit.

--
Regards,
Tom Ogilvy



"Cammy" wrote:

I am trying to create a macro which does an iterative process


I have a spreadsheet which performs a complicated series of calculations
based on a generic formula.

the 2 inputs that I have i would like to be changed are based in 2 cells and
I would like the value of the input (at the touch of a buttton) changed in an
iterative process ie a + 2; a+4 .... a+40, date+1day;
date+2day...date+20days. and then the result foudn in cell (x,y) I would
like paste into a matrix. a x b.

I presume that there is some standard coding which contains a looop function
which can perform this task,
Can anyone help?

Just because you don't get it doesn't mean it doesn't do exactly what you
asked.
Change M1 to be the cell in the upper left corner of your labels. I have
adjusted the code to look at A1:A3 rather than A1:C1, but you could have done
that yourself.


Sub Iterate()
dim i as Long, j as long
for i = 1 to 20
for j = 1 to 20
' increment number
Range("A1").Value = Range("A1").Value + 2
' increment date
Range("A2").Value = Range("A2").Value + 1
' write results
Range("M1").Offset(j,i).Value = Range("A3").Value
Next j
Next i
End Sub


--
Regards,
Tom Ogilvy


"Cammy" wrote:

sorry I don't get how this is tranferring the outut value (I need a cut and
pastesepcial as a value) created from the formula with inputs a, b into an
actual matrix?
for example
cell a1 = input a
cell a2 = input b
cell a3 = outputy
with an output matrix created:
a-4 a-2 a a+2 a+4
b
b+1
b+2
b+3

"Tom Ogilvy" wrote:

Sub Iterate()
dim i as Long, j as long
for i = 1 to 20
for j = 1 to 20
' increment number
Range("A1").Value = Range("A1").Value + 2
' increment date
Range("B1").Value = Range("B1").Value + 1
' write results
Range("M1").Offset(i,j).Value = Range("C1").Value
Next j
Next i
End Sub

Adjust locations and loop limits to suit.

--
Regards,
Tom Ogilvy



"Cammy" wrote:

I am trying to create a macro which does an iterative process


I have a spreadsheet which performs a complicated series of calculations
based on a generic formula.

the 2 inputs that I have i would like to be changed are based in 2 cells and
I would like the value of the input (at the touch of a buttton) changed in an
iterative process ie a + 2; a+4 .... a+40, date+1day;
date+2day...date+20days. and then the result foudn in cell (x,y) I would
like paste into a matrix. a x b.

I presume that there is some standard coding which contains a looop function
which can perform this task,
Can anyone help?

It still is not working
say the function in the cell A3 is a simple multiplication of A1*A2
and I have created a matrix in cell A7
1 3 5 7 9 ...20
1
2
3
4
5 ...20


and I now use this function

For i = 1 To 20
For j = 1 To 20
' increment Number
Range("A1").Value = Range("A1").Value + 2
' increment date
Range("A2").Value = Range("A2").Value + 1
' write results
Range("A7").Offset(i, j).Value = Range("A3").Value
Next j
Next i
End Sub


1 3 5 7 9 etc
1
2
3
4
5 etc

the result I get for the 1x 1 part of the matrix comes out as 6????? when
this should equal 1!

"Tom Ogilvy" wrote:

Just because you don't get it doesn't mean it doesn't do exactly what you
asked.
Change M1 to be the cell in the upper left corner of your labels. I have
adjusted the code to look at A1:A3 rather than A1:C1, but you could have done
that yourself.


Sub Iterate()
dim i as Long, j as long
for i = 1 to 20
for j = 1 to 20
' increment number
Range("A1").Value = Range("A1").Value + 2
' increment date
Range("A2").Value = Range("A2").Value + 1
' write results
Range("M1").Offset(j,i).Value = Range("A3").Value
Next j
Next i
End Sub


--
Regards,
Tom Ogilvy


"Cammy" wrote:

sorry I don't get how this is tranferring the outut value (I need a cut and
pastesepcial as a value) created from the formula with inputs a, b into an
actual matrix?
for example
cell a1 = input a
cell a2 = input b
cell a3 = outputy
with an output matrix created:
a-4 a-2 a a+2 a+4
b
b+1
b+2
b+3

"Tom Ogilvy" wrote:

Sub Iterate()
dim i as Long, j as long
for i = 1 to 20
for j = 1 to 20
' increment number
Range("A1").Value = Range("A1").Value + 2
' increment date
Range("B1").Value = Range("B1").Value + 1
' write results
Range("M1").Offset(i,j).Value = Range("C1").Value
Next j
Next i
End Sub

Adjust locations and loop limits to suit.

--
Regards,
Tom Ogilvy



"Cammy" wrote:

I am trying to create a macro which does an iterative process


I have a spreadsheet which performs a complicated series of calculations
based on a generic formula.

the 2 inputs that I have i would like to be changed are based in 2 cells and
I would like the value of the input (at the touch of a buttton) changed in an
iterative process ie a + 2; a+4 .... a+40, date+1day;
date+2day...date+20days. and then the result foudn in cell (x,y) I would
like paste into a matrix. a x b.

I presume that there is some standard coding which contains a looop function
which can perform this task,
Can anyone help?

Also I notice that even if I modify the inputs to A1=-1 and A2=0 to give the
1x1 part of the matrix to equal 1 the 1x 20 part of the matrix (which should
equal 20) is now giving me a number of 289941, which is also incorrect.
I think the rows and the columns may have to be filled sequentially as the
inputs are modified and the orginal input is not 'remembered'.
Help!

"Cammy" wrote:

It still is not working
say the function in the cell A3 is a simple multiplication of A1*A2
and I have created a matrix in cell A7
1 3 5 7 9 ...20
1
2
3
4
5 ...20


and I now use this function

For i = 1 To 20
For j = 1 To 20
' increment Number
Range("A1").Value = Range("A1").Value + 2
' increment date
Range("A2").Value = Range("A2").Value + 1
' write results
Range("A7").Offset(i, j).Value = Range("A3").Value
Next j
Next i
End Sub


1 3 5 7 9 etc
1
2
3
4
5 etc

the result I get for the 1x 1 part of the matrix comes out as 6????? when
this should equal 1!

"Tom Ogilvy" wrote:

Just because you don't get it doesn't mean it doesn't do exactly what you
asked.
Change M1 to be the cell in the upper left corner of your labels. I have
adjusted the code to look at A1:A3 rather than A1:C1, but you could have done
that yourself.


Sub Iterate()
dim i as Long, j as long
for i = 1 to 20
for j = 1 to 20
' increment number
Range("A1").Value = Range("A1").Value + 2
' increment date
Range("A2").Value = Range("A2").Value + 1
' write results
Range("M1").Offset(j,i).Value = Range("A3").Value
Next j
Next i
End Sub


--
Regards,
Tom Ogilvy


"Cammy" wrote:

sorry I don't get how this is tranferring the outut value (I need a cut and
pastesepcial as a value) created from the formula with inputs a, b into an
actual matrix?
for example
cell a1 = input a
cell a2 = input b
cell a3 = outputy
with an output matrix created:
a-4 a-2 a a+2 a+4
b
b+1
b+2
b+3

"Tom Ogilvy" wrote:

Sub Iterate()
dim i as Long, j as long
for i = 1 to 20
for j = 1 to 20
' increment number
Range("A1").Value = Range("A1").Value + 2
' increment date
Range("B1").Value = Range("B1").Value + 1
' write results
Range("M1").Offset(i,j).Value = Range("C1").Value
Next j
Next i
End Sub

Adjust locations and loop limits to suit.

--
Regards,
Tom Ogilvy



"Cammy" wrote:

I am trying to create a macro which does an iterative process


I have a spreadsheet which performs a complicated series of calculations
based on a generic formula.

the 2 inputs that I have i would like to be changed are based in 2 cells and
I would like the value of the input (at the touch of a buttton) changed in an
iterative process ie a + 2; a+4 .... a+40, date+1day;
date+2day...date+20days. and then the result foudn in cell (x,y) I would
like paste into a matrix. a x b.

I presume that there is some standard coding which contains a looop function
which can perform this task,
Can anyone help?

Maybe this is what you want:

Sub abc()
Range("A1:A2").Value = 1
k = -1
For i = 1 To 20
k = k + 2
Range("A7").Offset(i, 0).Value = k
Range("A1").Value = k
Range("A2") = 1
For j = 1 To 20
Range("A7").Offset(0, j).Value = j
Range("A7").Offset(i, j).Value = Range("A3").Value
Range("A2").Value = j
Next j
Next i
End Sub

--
Regards,
Tom Ogilvy


"Cammy" wrote:

Also I notice that even if I modify the inputs to A1=-1 and A2=0 to give the
1x1 part of the matrix to equal 1 the 1x 20 part of the matrix (which should
equal 20) is now giving me a number of 289941, which is also incorrect.
I think the rows and the columns may have to be filled sequentially as the
inputs are modified and the orginal input is not 'remembered'.
Help!

"Cammy" wrote:

It still is not working
say the function in the cell A3 is a simple multiplication of A1*A2
and I have created a matrix in cell A7
1 3 5 7 9 ...20
1
2
3
4
5 ...20


and I now use this function

For i = 1 To 20
For j = 1 To 20
' increment Number
Range("A1").Value = Range("A1").Value + 2
' increment date
Range("A2").Value = Range("A2").Value + 1
' write results
Range("A7").Offset(i, j).Value = Range("A3").Value
Next j
Next i
End Sub


1 3 5 7 9 etc
1
2
3
4
5 etc

the result I get for the 1x 1 part of the matrix comes out as 6????? when
this should equal 1!

"Tom Ogilvy" wrote:

Just because you don't get it doesn't mean it doesn't do exactly what you
asked.
Change M1 to be the cell in the upper left corner of your labels. I have
adjusted the code to look at A1:A3 rather than A1:C1, but you could have done
that yourself.


Sub Iterate()
dim i as Long, j as long
for i = 1 to 20
for j = 1 to 20
' increment number
Range("A1").Value = Range("A1").Value + 2
' increment date
Range("A2").Value = Range("A2").Value + 1
' write results
Range("M1").Offset(j,i).Value = Range("A3").Value
Next j
Next i
End Sub


--
Regards,
Tom Ogilvy


"Cammy" wrote:

sorry I don't get how this is tranferring the outut value (I need a cut and
pastesepcial as a value) created from the formula with inputs a, b into an
actual matrix?
for example
cell a1 = input a
cell a2 = input b
cell a3 = outputy
with an output matrix created:
a-4 a-2 a a+2 a+4
b
b+1
b+2
b+3

"Tom Ogilvy" wrote:

Sub Iterate()
dim i as Long, j as long
for i = 1 to 20
for j = 1 to 20
' increment number
Range("A1").Value = Range("A1").Value + 2
' increment date
Range("B1").Value = Range("B1").Value + 1
' write results
Range("M1").Offset(i,j).Value = Range("C1").Value
Next j
Next i
End Sub

Adjust locations and loop limits to suit.

--
Regards,
Tom Ogilvy



"Cammy" wrote:

I am trying to create a macro which does an iterative process


I have a spreadsheet which performs a complicated series of calculations
based on a generic formula.

the 2 inputs that I have i would like to be changed are based in 2 cells and
I would like the value of the input (at the touch of a buttton) changed in an
iterative process ie a + 2; a+4 .... a+40, date+1day;
date+2day...date+20days. and then the result foudn in cell (x,y) I would
like paste into a matrix. a x b.

I presume that there is some standard coding which contains a looop function
which can perform this task,
Can anyone help?

Thanks for your help Tom,

I have actually solved the problem now using a much longer coding sequence
(based on the orgoinal one you supplied me with) where I loop through the i
sequence and then loop through the j sequence later on whilst copying and
saving the original input values to prevent the later calculations being
affected... see below. This is to solve for a symmetric matrix
a-2, a-1, a, a+1, a+2
date
date + 1 etc

: )

Sub matrix()
' prepares the matrix headings
Range("A1").Select
Selection.Copy
Sheets("Sheet1").Select
Range("V7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.NumberFormat = "0"
Range("A2").Select
Selection.Copy
Sheets("Sheet1").Select
Range("A8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.NumberFormat = "0"

'inserts the matrix numbers
For j = 1 To 20
For i = 1 To 20
Range("A1").Value = Range("A1").Value + 2
Range("V7").Offset(j, i).Value = Range("A3")
Next i

Range("V7").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Range("A1").Value = Range("A1").Value
Range("V7").Offset(j, 0).Value = Range("A3")

For i = 1 To 20
Range("A1").Value = Range("A1").Value - 2
Range("V7").Offset(j, -i).Value = Range("A3")
Next i

Range("V7").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("A2").Value = Range("A2").Value + 1
Next j

Range("A8").Select
Selection.Copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

' increment Number
' Range("A1").Value = Range("A1").Value + 2 * i
' increment date
' Range("A2").Value = Range("A2").Value + j
' write results
' Range("A7").Offset(i + 1, j + 1).Value = Range("A3").Value
' Next i

'For i = 0 To 19
'For j = 0 To 19

' increment Number
' Range("A1").Value = Range("A1").Value + 2
' increment date
' Range("A2").Value = Range("A2").Value + 1
' write results
' Range("A7").Offset(i + 1, j + 1).Value = Range("A3").Value
'Next j
'Next i


End Sub

"Tom Ogilvy" wrote:

Maybe this is what you want:

Sub abc()
Range("A1:A2").Value = 1
k = -1
For i = 1 To 20
k = k + 2
Range("A7").Offset(i, 0).Value = k
Range("A1").Value = k
Range("A2") = 1
For j = 1 To 20
Range("A7").Offset(0, j).Value = j
Range("A7").Offset(i, j).Value = Range("A3").Value
Range("A2").Value = j
Next j
Next i
End Sub

--
Regards,
Tom Ogilvy


"Cammy" wrote:

Also I notice that even if I modify the inputs to A1=-1 and A2=0 to give the
1x1 part of the matrix to equal 1 the 1x 20 part of the matrix (which should
equal 20) is now giving me a number of 289941, which is also incorrect.
I think the rows and the columns may have to be filled sequentially as the
inputs are modified and the orginal input is not 'remembered'.
Help!

"Cammy" wrote:

It still is not working
say the function in the cell A3 is a simple multiplication of A1*A2
and I have created a matrix in cell A7
1 3 5 7 9 ...20
1
2
3
4
5 ...20


and I now use this function

For i = 1 To 20
For j = 1 To 20
' increment Number
Range("A1").Value = Range("A1").Value + 2
' increment date
Range("A2").Value = Range("A2").Value + 1
' write results
Range("A7").Offset(i, j).Value = Range("A3").Value
Next j
Next i
End Sub


1 3 5 7 9 etc
1
2
3
4
5 etc

the result I get for the 1x 1 part of the matrix comes out as 6????? when
this should equal 1!

"Tom Ogilvy" wrote:

Just because you don't get it doesn't mean it doesn't do exactly what you
asked.
Change M1 to be the cell in the upper left corner of your labels. I have
adjusted the code to look at A1:A3 rather than A1:C1, but you could have done
that yourself.


Sub Iterate()
dim i as Long, j as long
for i = 1 to 20
for j = 1 to 20
' increment number
Range("A1").Value = Range("A1").Value + 2
' increment date
Range("A2").Value = Range("A2").Value + 1
' write results
Range("M1").Offset(j,i).Value = Range("A3").Value
Next j
Next i
End Sub


--
Regards,
Tom Ogilvy


"Cammy" wrote:

sorry I don't get how this is tranferring the outut value (I need a cut and
pastesepcial as a value) created from the formula with inputs a, b into an
actual matrix?
for example
cell a1 = input a
cell a2 = input b
cell a3 = outputy
with an output matrix created:
a-4 a-2 a a+2 a+4
b
b+1
b+2
b+3

"Tom Ogilvy" wrote:

Sub Iterate()
dim i as Long, j as long
for i = 1 to 20
for j = 1 to 20
' increment number
Range("A1").Value = Range("A1").Value + 2
' increment date
Range("B1").Value = Range("B1").Value + 1
' write results
Range("M1").Offset(i,j).Value = Range("C1").Value
Next j
Next i
End Sub

Adjust locations and loop limits to suit.

--
Regards,
Tom Ogilvy



"Cammy" wrote:

I am trying to create a macro which does an iterative process


I have a spreadsheet which performs a complicated series of calculations
based on a generic formula.

the 2 inputs that I have i would like to be changed are based in 2 cells and
I would like the value of the input (at the touch of a buttton) changed in an
iterative process ie a + 2; a+4 .... a+40, date+1day;
date+2day...date+20days. and then the result foudn in cell (x,y) I would
like paste into a matrix. a x b.

I presume that there is some standard coding which contains a looop function
which can perform this task,
Can anyone help?