puting quotes in a string for a formula

[t16turbo]

I have some formula that I need to enter in a cell and fill down using a macro

Range("C2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],[sheamcat.xls]Sheet1!C1:C4,3,0)"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],[sheamcat.xls]Sheet1!C1:C4,4,0)"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=IF(O2=1, D2, IF(O2>0.49, "Very Likely Projects", "Prospects"))
    Range("C2:E2").Copy

the last forumla requires quote marks around the 'Very Likely Projects' and the 'Prospects'

writing it like this in VBA causes an error as it sees the first quote as the end of the statement.
how can I get around this?

 

[PHV]

ActiveCell.FormulaR1C1 = "=IF(O2=1, D2, IF(O2>0.49, ""Very Likely Projects"", ""Prospects""))"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[woodyinoz]

Put an extra set of quotes around the "Very Likely Projects" and "Prospects"...

This will give you

ActiveCell.FormulaR1C1 = "=IF(O2=1, D2, IF(O2>0.49, 
""Very Likely Projects"", ""Prospects""))

Hope this works for you,

Woody

 

[t16turbo]

thats great, thank you both.

just noticed now that the cell references are returned with single quotes around them! GRRRR

I replaced them with RC (as with my VLOOKUP formulas) to get aroud the problem:

this is for anyone who may be reading this thread to solve a similar problem.

"=IF((RC[+10])=1, RC[-1], IF(RC[+10]>0.49,""Very Likely Projects"",""Prospects""))"

were RC is the cell that the formula is in, and -1 being one cell to the left, +10 being 10 cells to the right!

 

[GlennUK]

Or use Formula instead of FormulaR1C1:

ActiveCell.Formula = "=IF(O2=1, D2, IF(O2>0.49, ""Very Likely Projects"", ""Prospects""))"

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[t16turbo]

can anyone tell me what the R1C1 bit does?
I'm only using that as I recorded a macro with a text string in a cell and it was in that format...

 

[GlennUK]

FormulaR1C1 allows you to enter formulae in Row and Column reference type strings ( try changing your worksheet setting, with Tools/Options/General and ticking the R1C1 Reference Style check box, and have a look at your formulae afterwards ).

Formula lets you enter formulae in the usual A1/B2 way.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[t16turbo]

as an assition to this - I now have to put a formula in that requires a range.

here's my code

A_variable = Cells(Rows.Count, "A").End(xlUp).Row
'Specify Range
Range("Q1:Q" & A_variable).Select

'############ insert subtotals
'sub totals
Sheets("CombinedFOB").Select
Range("A2").End(xlDown).Offset(1, 14).Select
ActiveCell.FormulaR1C1 = "Sub total (filtered projects)"
Range("A2").End(xlDown).Offset(1, 16).Select
ActiveCell.formula = "=SUBTOTAL(9,"&"Q1:Q"&A_variable&")"

but the formula is playing up again, as it is expecting the end of statement.

how can I put the range determined using the a_variable into that formula?

 

[woodyinoz]

Try this matey,

ActiveCell.Formula = "=SUBTOTAL(9,&""Q1:Q""& A_variable)"

Woody

 

[PHV]

Perhaps this ?
ActiveCell.formula = "=SUBTOTAL(9,Q1:Q" & A_variable & ")"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[vbap]

Lookup the .filldown method for a range. You might find this a little better then coping the row range to paste in next row.

Also you may have to trim(str(A_variable)) if A_variable was originally an integer to make as a string.