1

I've got a set of equations which I'd like to be repeated taking variables from the next row down for each of the columns at which each variable is located. I am a beginner, so my coding is probably not to scratch:

Sub Iteration()

Dim al, ab, ae As Double
Dim as1, as2 As Double 
'etc

as1 = Range("CG7")
as2 = Range("CG8")
aA1 = Range("BQ7")
'etc

intCounter = 0
For intCounter = 1 To 10000

  Let x = ((aN1 * 1000) - (as1 * aA1) + (as2 * aA2)) / (al * fc * ae * ab)

  Let x2 = ((aN12 * 1000) - (as12 * aA12) + (as22 * aA22)) / (al2 * fc2 * ae2 * ab2)

Next

 Sheets("Sheet1").Range("CJ7").Value = x

End Sub

I've done this for several variables which I've set as the range relative to each variable value. And then for the next row I've had to redo the whole equation and set the variables again for the next row down. Is there any way to set the variables (possibly with a relative cell reference?) which will skip to the cell in the next row for the next calculation? Consider also that there are 36 rows for calculation and about 9 variables!

pnuts
  • 58,317
  • 11
  • 87
  • 139
user1769265
  • 11
  • 2

1 Answers1

0

If I understand you correctly, how about something like this:

Sub Iteration()

Dim al, ab, ae As Double
Dim as1, as2 As Double 
'etc

intCounter = 0
For intCounter = 0 To 10000

    as1 = Range("CG7").Offset(intCounter)
    as2 = Range("CG8").Offset(intCounter)
    aA1 = Range("BQ7").Offset(intCounter)
   'etc

  Let x = ((aN1 * 1000) - (as1 * aA1) + (as2 * aA2)) / (al * fc * ae * ab)

  Let x2 = ((aN12 * 1000) - (as12 * aA12) + (as22 * aA22)) / (al2 * fc2 * ae2 * ab2)

Next

 Sheets("Sheet1").Range("CJ7").Value = x

End Sub
Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
  • That is good if I want to run through the calculations once, but I'd like to run through the calculation for each row, then jump back to the top to run through all of them again until a converged value is obtained. – user1769265 Oct 24 '12 at 09:01
  • @user1769265 -> in that case, can't you just change where you place the offset and maybe wrap a loop around the loop you already have? – Scott Holtzman Oct 24 '12 at 13:00
  • yes, so I've set up the first intcounter to run through the rows of cells that I'm using for calculation, I'm just trying to establish a loop which will stop when the final value has iterated, though I'm having some trouble relating the value of x for each case to its iterated value. Therefore i don't know what to put after the Until Command. I'd like to check the iterated value against the previous value of x and stop calculating when the deviation is less than 0.001 for example, for all cases of x. – user1769265 Oct 24 '12 at 14:16