Identifying cells in a For Next loop

[aexley]

Good afternoon,

I have a simple formula to run on a number of cells. The formula references the cells next to it and, according to their values, adjusts the value of each cell in turn. What I'm having trouble with is working out a For Next loop that identifies each cell in turn in order for the formula to erun correctly. At the moment I only have a 3X3 matrix but it is possible that this may be increased to the 100's.

I could enter the formula 9 times but this would soon become impractical.

If it helps here is the formula I'm running on each cell(the example is for cell A1):

If Cells(1, 2) > 6 And Cells(2, 1) > 6 Then
A1.Value = A1 + 1
Else
If Cells(1, 2) < 4 And Cells(2, 1) < 4 Then
A1.Value = A1 - 1
End If
End If

I will be making the formula more complex in the future but I need to get the basic setup right first.

Thanks in advance,

aexley

 

[Zathras]

It might help if you could explain what it is that you are trying to do, but here is a routine that is functionally equivalent to what you have posted:
[blue]

Sub FiddleFaddle()
Dim c As Range
  For Each c In Range("A1:C3")
    If c.Offset(0, 1) > 6 And c.Offset(1, 0) > 6 Then
      c.Value = c.Value + 1
    Else
      If c.Offset(0, 1) < 4 And c.Offset(1, 0) < 4 Then
        c.Value = c.Value - 1
      End If
    End If
  Next c
End Sub

[/color]

 

[aexley]

Thanks Zathras I'll give it a shot.

I don't think it will help but it's a personal project to see if I can create a simple routine that simulates water evaporating from a raincloud.

The idea is that the cloud begins with each droplet (cell) at a random energy value and then changes as energy is transferred through the cloud dependant on the value of its neighbour eventually some droplets will reach a level of energy at which they evaporate and are removed from the cloud. It's a curiosity project more than anything.

Hope that helps (realise it probably won't)

Thanks

 

[Zathras]

Very interesting. But in order to do that I would think you would need to consider more than two &quot;neighbors&quot; (perhaps even need to go 3-D) and use an algorithm that calculates the next state value for all cells before actually changing any. In other words, have two sets of numbers (or matrices), say &quot;A&quot; and &quot;B&quot; and set up the values in &quot;B&quot; from a calculation of &quot;A&quot; then set up the values in &quot;A&quot; from a calculation of &quot;B&quot; and so on.

As a warm-up coding exercise, you might consider starting with John Horton Conway's &quot;Game of Life&quot; which illustrates the processing required in a simpler way (only 2-D). (Google search: &quot;game of life&quot