Search:

# How can I fill all combinations of x and y in columns A and B

Asked By: Seth    Date: Nov 24    Category: MS Office    Views: 993

I have a problem that I am trying to solve for days now but no
success. In essence I am making a code which will calculate certain
number at certain coordinates. The main problem is that code
calculates the number then write coordinates and the result in the
first row. Then it calculates the next number and writes the next
row using offset:

ActiveCell.Value = myX
ActiveCell.Offset(0, 1).Value = myY
ActiveCell.Offset(0, 2).Value = myTotal
Cells(ActiveCell.RoW + 1, 1).Select

...by the time it gets to row 1400 it gets fairly slow.

However, when I run it again and again (without quitting excel) it
gets very, very slow. I am trying to do arrays and write them to a
range rather than individual cells.

Can anyone give me some clue how to write such a code (array to a
range) for the following example:

Let x = 1 to 10
Let y = 1 to 10
Let z = f(z)

x and y change such as: (x1,y1), (x1,y2), (x1,y3),...,(x10,y8),
(x10,y9), (x10,y10).

How can I then fill all combinations of x and y in columns A and B.
Of course the third column should be z which is the value of f(z)
function for those coordinates.

Share:

Try something like:

Dim vData(1 To 100, 1 To 3)
iRow = 0
For x = 1 To 10: For y = 1 To 10
iRow = iRow + 1
vData(iRow, 1) = x
vData(iRow, 2) = y
vData(iRow, 3) = x + y
Next y: Next x
Range("A1:C100") = vData