Question

Write an excel macro that calculates the average of two columns separately and writes them down using the range object command.

Please help me with this code I have no clue how to do it! Will rate.

Answer 1

If there is a sales report in excel if we consider Column A is Employe and Column B is Sales Y1, Column C is Sales Y2 and Column D is Average Sales. If we have 4 employes names and and each employee has two sales means year 1 sale and year2 sale(Uploaded in picture for reference).

First Employe name is Jim Bob and sale Y1 value $65,923.00 and Sale Y2 value is $70,186.00. It means Column A is Jim Bob Column B is Sales Y1(Year) $65,923.00 and Column C Sales Y2(Year) $70,186.00 for Average Sale we can take Column D

If we want to run a Macro we need VB script, so the formula for average of two columns separately with using range object command Range(D4).Formula = "=AVERAGE(B4:C4)"

In VB Script we can take new module and we need to type the formulae as given below:

Sub Enter_Formulas()

'Range(D4).Formula = "=AVERAGE(B4:C4)"

End Sub

This formula we need to type in VB Script and in Excel while implementirng the formula we run the Macros with Alt+F8 then we will get the actual range of the value in Average Sale Column D value is $68,054.50 for only Jim Bob employee average sale and we can get all the employee with same formula

Note: For the reference I have attached the picture from Excel and VB Script image. Please find the attached images with formulaes.

.