VB Problem with COUNTIF

[yensid00]

I am fairly new to the world of VB and am having a problem that I can't seem to figure out.

I have a large data file that changes in size and number on a daily basis. I have three columns that contains a numerical amount that I sum up and then a series of columns with text where I am trying to count the number of times an "X" appears. I then get the sum of the numerical and text columns together. I was originally using a formula with COUNTA for the text section but discovered that since the info is coming from a .CSV file that there is hidden text that is being counted and returning an incorrect number. Here is my original code:

ActiveCell.FormulaR1C1 = "=SUM(RC[8]:RC[10])+COUNTA(RC[11]:RC[25])"

I tried changing it to a COUNTIF so that it will only return the amount of spaces containing an "X" but the new code always gives an error that says:

Compile error: Unexpected: end of statement

The new formula I am attempting to use is:

ActiveCell.FormulaR1C1 = "=SUM(RC[8]:RC[10])+COUNTIF(RC[11]:RC[25],"X")"

What am I doing wrong???

 

[Jonmo1]

when the formula you want entered into the cell contains quotes, you have to double up the quotes in the VBA code...

try

veCell.FormulaR1C1 = "=SUM(RC[8]:RC[10])+COUNTIF(RC[11]:RC[25],""X"")"

Hope that helps..

 

[twistinginthewind]

Looks like your quotes are entered correctly try this...

ActiveCell.FormulaR1C1 = "=SUM(RC[8]:RC[10])+COUNTIF(RC[11]:RC[25],""X"")"

 

[schielrn]

It doesn't like your quotes. Maybe try:

ActiveCell.FormulaR1C1 = "=SUM(RC[8]:RC[10])+COUNTIF(RC[11]:RC[25]," & """ & "X" & """ & ")"

Or if that doesn't work maybe try:

dim wordX as string
wordX = "X"
ActiveCell.FormulaR1C1 = "=SUM(RC[8]:RC[10])+COUNTIF(RC[11]:RC[25]," & wordX & ")"

Quotes are always wierd in VBA.

 

[yensid00]

That did the trick!! Thanks everyone!!