Excel-convert array formulas to VBA, ignore blank rows from formula
Budget: $30 – $250 AUD
Hi. I've got a spreadsheet with 3 tables. My objective is to associate multiple accessories with a machine and display these in a table. My spreadsheet works as expected but I would like 2 more modifications:
1. There are array formulas in each cell of the Working Spreadsheet. This is fine for a handful of machines as in the sample file but in reality there could be thousands, and this affects computer performance. I would like to substitute code for these arrays.
2. The Working Spreadsheet has many columns for each Accessory Type to account for the possibility of many accessories per type, per machine. However most devices only have a few, which means the formulas return blank values and a CHAR(10) return, which leaves spaces in the result. This doesn't look good when I display the accessories. I would like the formula to ignore cells where the array formulas return no result.
1. There are array formulas in each cell of the Working Spreadsheet. This is fine for a handful of machines as in the sample file but in reality there could be thousands, and this affects computer performance. I would like to substitute code for these arrays.
2. The Working Spreadsheet has many columns for each Accessory Type to account for the possibility of many accessories per type, per machine. However most devices only have a few, which means the formulas return blank values and a CHAR(10) return, which leaves spaces in the result. This doesn't look good when I display the accessories. I would like the formula to ignore cells where the array formulas return no result.