[dba-Tech] Programmatically change cell formulae in Ecel

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
>
>