Need Help for Desirability Calculator (moving squares with numbers around)

[Philon]

Hi,
I'm new to this forum and don't know much about excel. I want to make
desirability calculator wich is related to a game. In the game ther
are structures and each structure has a desirability affect on it
environment. When a few structures are placed next to each other it'
too tedious work to calculate their total affect.
I hope the attachment will be clear enough to show what I want to do.
There are around 30 structures but as an example I have shown th
desirability affect of two of them; an oracle and a garden. When yo
open the file you will see these two structures and their affect.
By scrolling a bit down you will see these two structues moved close
so that some tiles are withing the range of both structures. I hav
manually calculated this part since there are only two structures but
need a calculator for complicated designs.

I guess making such a calculator will be a complicated work but I wan
to do it even if it takes a year. Could you give me some links an
tips?
Thanks,
Philo

Attachment filename: philon001.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=61498

 

[BrianB]

Most of us will not download files.

I suggest you describe 2 of your simpler structures and show how the
interact

 

[jeff]

Hi,

I would approach your task like this:
Write a macro to read each object pattern into
an array and store this into a file. You can
select the area (i.e. position "Garden" set at A1:G7)
the following would read in the values for
each cell. Dimension 1 is the object number, two
& three are the row and columns.

Sub SetRange()
Dim R As Range
Set R = Selection
ObjNum = 1 ' input this dynamically or set to
1..2..3,,,etc for each
' object
j = 0
For Each c In R
j = j + 1
If j > 1 And strtrow > 1 Then
RArray(ObjNum, c.Row - strtrow, c.Column) = c.Value
Else
RArray(ObjNum, c.Row, c.Column) = c.Value
If j = 1 Then strtrow = c.Row
End If
Next c
MsgBox "Got Object " & ObjNum
' now print out the array to a file for later reading in
all at once
End Sub

Now you can write another (main calculator) macro
which, after reading in all the array data,
loops thru each object's array, and compares
its cells' positions against the next object using
the "intersect" function in VBA. (obviously it's a bit
trickier than I"m making it - you need to track object 1's
"area" via "Offset"s from its center address; and likewise
for each object's area you compare against.

Not a trivial bit of coding, but might be fun..

good luck
jeff