Question

Using a For Next-structure in Excel

Write a loop that will calculate the sum of every third integer, beginning with i = 2 (i.e., calculate the sum 2 + 5 + + 11 + ...) for all values of i that are less than 100. Submit one file with a macro for each loop structure shown below:

Answer 1

STEP 1 : Open your excel workbook. and press ALT + F11 to open VBE (Visual basic editor)

STEP 2: Right click on your project title from the project explorer. then click on INSERT -> MODULE.

STEP 3: Enter the visual basic code required in the white area on the right pane.

code:

Sub Sum_Every_Third() ' macro function name

Dim LCounter As Integer

  Application.ScreenUpdating = False ' to speedup computing

Application.Calculation = xlCalculationManual    ' to speedup computing

  totsum = 0

For LCounter = 2 To 99 Step 3   ' values from 2 to 99 with stepsize of 3

totsum = totsum + LCounter ' adding very third number

Next LCounter

MsgBox totsum ' to display the result in a message box

Range("A1") = totsum ' to display the result on the cell

  

   Application.ScreenUpdating = True   ' to speedup computing

   Application.Calculation = xlCalculationAutomatic ' to speedup computing

End Sub

press run button (play button) to run the script and verify the result.

STEP 4: Press CTRL + S to save the workbook. Enter workbook name and choose type as 'Excel Macro-Enabled Workbook'.

STEP 5: Press ALT+Q to quit VBE and return to your workbook window. Result will be displayed on cell A1.

If you want to run your custom macro, press ALT+F8 to open macro dialog. Select your macro name and cick on 'Run'. The result will be displayed in cell A1.

ALL THE BEST :D