Format of Cell with Array Formula - Using VBA

P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top