-
Performing calculations using VBA
Hi I am relatively new to using VBA, I have been playing around with simple commands and have reached the limit of what I can do. I am looking to perform a calculation of data using VBA that will do the same thing as this formula in excel 0.5*(G31+G30)*(H31-H30)+I30 returning only the a value in column P starting in row 31. This operation will need to be applied to the entire range of data in columns G and H starting in row 31
I don’t even know where to start on this, any help would be appreciated.
Thanks
-
Welcome to VBAX
Simplest way is to do the calculations on the sheet, then get rid of the formulae
[vba]
Option Explicit
Sub DoCalc()
Dim Rng As Range
Set Rng = Range(Cells(31, 7), Cells(Rows.Count, 7).End(xlUp)).Offset(, 9)
Rng.FormulaR1C1 = "=0.5*(RC[-9]+R[-1]C[-9])*(RC[-8]-R[-1]C[-8])+R[-1]C[-7]"
Rng.Value = Rng.Value
End Sub
[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
Thanks that works perfectly
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules