This page has 0 threads | Add post

Exercises

Finding our exercises useful? Please consider helping us to share more!

Software ==>	OfficeScripts (21 exercises)
Topic ==>	Arrays (4 exercises)
Level ==>	Average difficulty
Subject ==>	Office Scripts training

Use an array in Office Scripts to check the values of a range of cells.

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

Before you can do this exercise, you'll need to download and unzip this file.

Open the Choc Shop.xlsx file and create a new script. The aim of the exercise is to use an array to check that a range of cells contains only negative numbers.

A range of positive and negative numbers in Excel.

Each cost should be entered as a negative number.

Create an array which reads the values of cells F6:Q8 on the Inputs sheet using the getValues method.

Loop through the array to check if any value is a positive number. If so, highlight the corresponding cell in the worksheet in a different colour.

You can use the getCell method to refer to cells within a range by row and column numbers. For instance, getRange("F6:Q8").getCell(0, 1) will refer to cell G6.

Run the script to highlight the positive values.

Excel cells with positive numbers highlighted.

It should be easier to spot the positive numbers after you run the script.

Add code to change any positive number to its equivalent negative value and overwrite the value in the relevant cell.

If you'd like to revert the formatting of the cells back to their original colour you can apply the Input number style to the range using the setPredefinedStyle method.