Running an array through a calculator

[keith6226]

Hello all,

I have created a calculator using lookup tables and some other data to adjust value based on three inputs, date 1, date 2, city, and initial value. Basically, I am trying to get this calculator to work on a table filled with many rows of this data. The (simple) way I suppose would be to dupliucate this calculator (which fills 3 sheets) for each row, but I know there must be a way to do it in a cleaner way.

Right now the calculator references named ranges (cells), so i'nm thinking perhaps a macro with a do ... until loop to shift the position of the references and the output. This also seems rather complicated though, and I am not sure it is even possible. Failing this, I am thinking that some sort of array type formula will work?

Any suggestions would be most appreciated.

[DonkeyOte]

Welcome to the Board - unfortunately there's very little to go on here ... I think you will need to elaborate on what exactly is taking place - better yet post a dummy file with non-confidential nature that mimics your setup so we can both visualise what you're working with and get an idea of what it is you're attempting to do.

At present I'm afraid it reads along the lines of:

"I have this 'thing' that does 'stuff' and I need to make it more flexible - how can I do that ?"

Which I'm afraid is not much for us to work with.

[keith6226]

Ok, so all I am looking to do is input values from a given row into a "calculator sheet" and have it output a value to that same row. Here is what I have come up as far as that goes, but it does not seem to work right. Please let me know if there are any obvious syntax issues.

Thanks

Sub MeterCalc()
'Created 9/7/09 by Keith Rossman, Energy Trust of Oregon

Dim numRows As Integer

For x = 3 To numRows
Sheets("Tables").Select

'Initial and final dates
Range("C6").Select
ActiveCell.FormulaR1C1 = "=month(Aggregate!T" & x & ")"
Range("D6").Select
ActiveCell.FormulaR1C1 = "=day(Aggregate!T" & x & ")"
Range("E6").Select
ActiveCell.FormulaR1C1 = "=year(Aggregate!T" & x & ")"
Range("C7").Select
ActiveCell.FormulaR1C1 = "=month(Aggregate!X" & x & ")"
Range("D7").Select
ActiveCell.FormulaR1C1 = "=day(Aggregate!X" & x & ")"
Range("E7").Select
ActiveCell.FormulaR1C1 = "=year(Aggregate!X" & x & ")"

'City lookup
Range("P2").Select
ActiveCell.FormulaR1C1 = "=vlookup(Aggregate!E" & x & ",City,2,FALSE)"

'Output
Sheets("Aggregate").Select
Range("AC" & x).Select
ActiveCell.FormulaR1C1 = "=((Final-Initial)/Tables!I2)"


Next x
End Sub