Macro coding to create a matrix

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?
> > > > > > >