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