Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array's, Calculations and Results
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array's, Calculations and Results
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 -- Regards, Tom Ogilvy "smokiesandwine" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array's, Calculations and Results
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array's, Calculations and Results
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array's, Calculations and Results
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 -- regards, Tom Ogilvy "smokiesandwine" wrote in message ps.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Calculations with Calculated Results | Excel Worksheet Functions | |||
N/A results on vlookup with time calculations | Excel Worksheet Functions | |||
How can I output VBA function results (array's for example) to spreadsheet | Excel Worksheet Functions | |||
Help on performing calculations on formula results | Excel Worksheet Functions | |||
Getting results from VBA calculations into cells | Excel Programming |