For VBA excel code with anchor cell and offset style. Thanks. Bins data is presp

Question

For VBA excel code with anchor cell and offset style. Thanks. Bins data is prespecified but not Counts,it comes from running the code.

4). Using the worksheet Data in the attached file, write a sub that will do the following a). use cell A3 as an anchor cell b). locate the cell where the word Bins is located C). declare a dynamic array (name it Bins) as a single and a dynamic array (name it Cnt) as an integer d). Count the number of bins using range properties (i.e., count, etc) e). re-dimension the array Bins to have the number of bins specified in the worksheet and determine in d) f). re-dimension the array Cnt to have the number of bins plus one g). write a sub that will count the number of times the "amount purchased" is below the first value in the Bins array and place in the first element of the array Cnt, the number of times the "amount purchased" is greater than the first value and less than or equal to the second value in the Bins array and place it in the second element of array Cnt, and so on. The last element of the array Cnt will include the number of times the "amount purchased" exceeds the last value in the Bins array h). write next to the column for the Bins, the numbers collected in the Cnt array. Note that the Cnt array has one more element than the Bins arra Your code should run for a different number of Bins and a different data set with A3 as anchor cell For the example given in the worksheet, the column for Counts will look as follows: Bins Counts 75 100 294 500 63 700 53 1000 61

Explanation / Answer

To select a cell in Excel, you have two basic methods: RANGE and CELLS:

Range works well for hard-coded cells. Cells works best with calculated cells, especially when you couple it with a loop:

Note that your focus does not change. Whatever cell you were in when you entered the loop is where you are when you leave the loop. This is way faster than selecting the cell, changing the value, selecting the next cell, etc. If you are watching the sheet, the values simply appear.

There are times when you are processing a list when you might want to look at the values in the same row, but a couple of columns over. You can accomplish this best with the .Offset clause. You might see the .Offset clause when you record a macro using relative references:

This is both confusing and overkill. Translated into English, it takes the current cell (ActiveCell) and selects the row that is one row down from the current row and in the same column. The “Range(“A1)” clause is not necessary. So if you want to stay in the current cell and read a value two columns to the right, you could use syntax like the following:

If you are in cell D254, the code above will reference the cell F254 and read its value into the variable strMyValue. This is far more efficient than selecting the cell two columns to the right, processing your data, then remembering to select two columns to the left and continue.

If you want to offset to a column to the left of you or a row above you, use a negative number. If you are in cell G254, the code ActiveCell.Offset(-3,-2).Select will select E251 (3 rows up and 2 columns left).

You can loop through a list much more efficiently with Offset. It is easier to program and way faster to execute.

You would probably want to format the salary for currency, but this is the general idea.

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.