Macro coding to create a matrix

[Guest]

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?

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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!

 

[Guest]

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!

 

[Guest]

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

 

[Guest]

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