Format of Cell with Array Formula - Using VBA

[Paige]

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 can’t 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 I’m 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

 

[Patrick Molloy]

what does the formula text itself look like?
=TRIM ( ????)

 

[Paige]

Hi, Patrick. It is:
{=VLOOKUP(TRIM($B5),TRIM($B$26:$D$41),3,FALSE)}