Array's, Calculations and Results

[smokiesandwine]

Storing formula's in an array, calculating SUM in the array then
returning result into a cell without formula. this basically does the
calculation however the formula is in the cell

so far i have .......

Sub FormulaTest()
Dim myFormula(0 To 5) As String
Dim myresult(0 To 5) As String
Dim myFlag As Boolean
Dim x As Integer
Dim i As Integer
Dim Result As String
Dim qwerty As Variant

'Select cells A10 to A15
Range("A10:A15") = ""

'Fill the array with formulas
myFormula(0) = "=SUM(10+12)"
myFormula(1) = "=SUM(20+12)"
myFormula(2) = "=SUM(30+12)"
myFormula(3) = "=SUM(40+12)"
myFormula(4) = "=SUM(50+12)"
myFormula(5) = "=SUM(60+12)"

Do Until myFlag = True

For i = 0 To 5

'NEED SOME CODE IN HERE } need to keep the result of the
formula and put it in the next cell

' ActiveCell = myFormula(i) } this will enter the
formula along with the result in the cell

ActiveCell.Offset(1, 0).Select } moves to the next cell
down

If i = 5 Then
myFlag = True: Exit For
End If
Next i
Loop

End Sub

 

[Tom Ogilvy]

Sub FormulaTest()
Dim myFormula(0 To 5) As String
Dim i As Long

'Select cells A10 to A15
Range("A10:A15").ClearContents

'Fill the array with formulas
myFormula(0) = "=SUM(10+12)"
myFormula(1) = "=SUM(20+12)"
myFormula(2) = "=SUM(30+12)"
myFormula(3) = "=SUM(40+12)"
myFormula(4) = "=SUM(50+12)"
myFormula(5) = "=SUM(60+12)"

For i = 0 To 5
Range("A10").offset(i,0).Value = Evaluate(myformula(i))
Next i

End Sub

 

[smokiesandwine]

Magic Tom

Thank you again , currently working on a macro for someone , first time
ive done any sort of excel programming.

Cheers again for a quick reply.

 

[smokiesandwine]

Hey Tom

I have this simple programming running through a array, basically
displayin the result in a cell then movin to the cell below. however i
basically i have 2340 formula from cell A1:AI110 (eg), each cell
contains a different formula. I want to put each formula into the array
to store , which is done , then run through each formula displayin the
correct result in the associated cell, (eg) a2 would be myformula(0) a3
would be myformula(1) and so fore, and say after 100 formula's move to
column B cell 1 and contunue running through the array.

Dont know how much sense this makes , hope u can help out , even if u
can give me the simple command of move to next cell , i feel i could
get this workin, alot of code but workin !!!

Cheers for any help

Kind Regards
David


Sub FormulaTest()
Dim myFormula(0 To 11) As String
Dim i As Long

'Select cells A10 to A15
Range("A10:B15").ClearContents

'Fill the array with formulas
myFormula(0) = "=SUM(10+12)" }
myFormula(1) = "=SUM(20+12)" }
myFormula(2) = "=SUM(30+12)" )
myFormula(3) = "=SUM(40+12)" )
myFormula(4) = "=SUM(50+12)" )

SUM will be replaced with =IF(AND
(OFFSET(Database!$D$1,$B7+G$2,0)=
$A7,HLOOKUP("Course_Start",Database!
$A$2:$O$1600,$B7+G$2,FALSE)>0),
HLOOKUP("Course_Start",Database!
$A$2:$O$1600,$B7+G$2,FALSE)," ")

myFormula(5) = "=SUM(60+12)" )
myFormula(6) = "=SUM(70+12)" }
myFormula(7) = "=SUM(30+12)" }
myFormula(8) = "=SUM(40+12)" }
myFormula(9) = "=SUM(50+12)" }
myFormula(10) = "=SUM(60+12)" }
myFormula(11) = "=SUM(70+12)" }

For i = 0 To 5

If formula(i) = formula(5) Then

'move to the next column

Else

Range("A10").Offset(i, 0).Value = Evaluate(myFormula(i))

Next i

End Sub

 

[Tom Ogilvy]

Sub ABC()
dim i as Long, cell as Range
Dim col as Range

' code that puts formula strings the the array arrFormulas()

for each col in Range("A1:AI110").columns
for each cell in col.cells
cell.Value = Evaluate(arrFormulas(i))
i = i + 1
next
Next

End sub