Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format of Cell with Array Formula - Using VBA
I have a form where the user selects a cell for a specific formula via
refedit, then selects whether he wants the TRIM function added to the formula or not. Just prior to inserting the formula, I determine the format of another cell and apply it to the cell that will contain the new formula. If TRIM is not part of the formula, everything works fine and the cell with the new formula has the correct format. However, if I add TRIM (and the formula is now an array formula), when it is put into the cell, the 'underlying' format is changed to TEXT, even though the displayed format via Format | Cells is Currency (which is what it should be), and I cant change it regardless of what I do. I've tried everything to correct this and have no idea what I'm doing wrong. Any ideas where Im going wrong????? The code: Dim ColIndexNumberCell As Range Dim ColIndexNumberCellAddress As String Dim LFormulaCellAddress As String This is the code that I use to format the cell where the new formula will go: Set ColIndexNumberCell = Range(SelectColIndexRefEdit.Text) ColIndexNumberCellAddress = ColIndexNumberCell.Address(False, True) With ActiveCell .NumberFormat = Range(ColIndexNumberCellAddress).NumberFormat End with Then I enter the formula WITHOUT trim (and thus not an array): Range(LFormulaCellAddress).Formula = Me.LFormula.Caption Then if the user selected TRIM, I take the original formula and change it to an array: Range(LFormulaCellAddress).FormulaArray = Range(LFormulaCellAddress).Formula |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell format for an array formula containing text | Excel Worksheet Functions | |||
Array Formula - Average from every other cell | Excel Discussion (Misc queries) | |||
Format range of cells for Array formula | Excel Programming | |||
Tricky array formula issue - Using array formula on one cell, then autofilling down a range | Excel Programming | |||
referencing the value of a cell containing an array formula | Excel Worksheet Functions |