RE: Format of Cell with Array Formula - Using VBA
- From: Paige <Paige@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 15 Sep 2009 09:00:01 -0700
Hi, Patrick. It is:
{=VLOOKUP(TRIM($B5),TRIM($B$26:$D$41),3,FALSE)}
"Patrick Molloy" wrote:
what does the formula text itself look like?.
=TRIM ( ????)
"Paige" wrote:
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
- References:
- Format of Cell with Array Formula - Using VBA
- From: Paige
- RE: Format of Cell with Array Formula - Using VBA
- From: Patrick Molloy
- Format of Cell with Array Formula - Using VBA
- Prev by Date: Re: Default value of msoControlComboBox
- Next by Date: Re: Subtotal Function
- Previous by thread: RE: Format of Cell with Array Formula - Using VBA
- Next by thread: Setting a variable for the userform itself?
- Index(es):
Relevant Pages
|
