[dba-Tech] Programmatically change cell formulae in Ecel

Thanks for the tip, Tina. Turns out that Charlotte's notion of concatenating
portions of the formula with Chr(39) in place of the single quotes. I don't
know why that did the trick but it did, so a solution is hand is better than
two failed attempts, as it were.

The really odd thing is that elsewhere in the code I use the straightforward
syntax of Range(A3.Formula) = "formula" and it works.

Arthur

On 12/6/07, Tina Norris Fields <tinanfields at torchlake.com> wrote:
>
> Arthur,
>
> Don't know if this will help, but I recently discovered while creating a
> criteria range for use in an Excel data extraction, that I had to use
> ="=Male" to get the criterion =Male to be understood by the formula to
> extract the records, which was
> =DCOUNT(r_SurveyData,"ResponseNumber",$I$12:$K$13).  The range I12:K13
> held the field names and the criteria.  In that range, for each field
> criterion I had to enclose the criterion in double quotes and precede
> that with the = sign.  For what it's worth.
>
> Tina
>
> Arthur Fuller wrote:
> > Sorry for the double-post but I'm desperate.
> >
> > I'm trying to change cell formulae in Excel using VBA code and am
> running
> > into two different problems (well, maybe problem 2 will disappear when I
> > figure out problem 1).
> >
> > I have a string stored like this:
> >
> > Dim strFormula as String
> > strFormula "=VLOOKUP(A6,'S:\Lgroup\Fundacct\Excel\!INVPORT\CSV Files\Sep
> > 07\[# of securities.xls]SOI'!$B$3:$C$100,2,TRUE)"
> >
> > I am trying to assign it to the formula of a cell like this:
> >
> > Range("E6").Formula = strFormula
> >
> >
> > What happens is that it gets pasted in as text, NOT as the formula. I
> tried
> > removing the equals sign and assigning the rest to the formula of the
> cell,
> > but that didn't work either.
> >
> > I'm way behind on a deadline and I have to get this working. Any
> assistance
> > greatly appreciated!
> >
> > TIA,
> > Arthur
> > _______________________________________________
> > dba-Tech mailing list
> > dba-Tech at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-tech
> > Website: http://www.databaseadvisors.com
> >
> >
> _______________________________________________
> dba-Tech mailing list
> dba-Tech at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-tech
> Website: http://www.databaseadvisors.com
>