How to put text into a VBA if statement instead of another number? 1

[RyanScharfy]

My original VBA statement does the following:

Starting in column Q, cell 2, it looks over to column P and if the value in column P is zero, it inserts a zero in the Q column using the If statement. This goes on for every cell down column Q until I reach the end of my list.

Here is the original VBA instruction

Dim NERGrowth As Long
NERGrowth = ActiveSheet.UsedRange.Rows.Count
Range("Q2:Q" & NERGrowth).FormulaR1C1 = "=IF(RC[-1]=0,0,(RC[-2]-RC[-1])/RC[-1])"

What I'd LIKE to do is instead of putting in Zero, I'd like to put in the text "New". I've tried a couple basic things, but I'm having no luck.

Any ideas?

Thanks,

Ryan

 

[JPMontreal]

Hi
Range("Q2:Q" & NERGrowth).FormulaR1C1 = "=IF(RC[-1]=0,"New",(RC[-2]-RC[-1])/RC[-1])"



Jean-Paul
Montreal
jp@solutionsvba.com
mtljp2@sympatico.ca

 

[RyanScharfy]

Hi Jean Paul,

Thanks for the tip, but that doesn't work. I get a compiler error message "Expected: end of statement".

 

[Zathras]

Since you are inside a string constant, you need to double the quotes to get it to come out as one:

[/b][/code] Range("Q...,[/code][red]

""New""

[/color]

,...[-1])"

[/b]

 

[SkipVought]

Hi,

    NERGrowth = ActiveSheet.UsedRange.Rows.Count
    With Range(Cells(2, "Q"), Cells(NERGrowth, "Q"))
      If .Offset(0,-1).Value = 0 Then _   
        .Value = "New"
    End With

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[RyanScharfy]

Thanks you all. The quickest solution I used was to use the double "" to enclose the text.

Thank you!