[RESOLVED] problem with ' in a formula

Hi
I want to write a formula into a cell
I used the following code:
oXLSheet.cells(20,2).Formula="=SUMPRODUCT(COUNTIF(INDIRECT('Sheet1'!D2:D100"),"*Economics*"))" but it considered ' as a comment so only =SUMPRODUCT(COUNTIF(INDIRECT( is considered as a formula.('Sheet1'!D2:D100"),"*Economics*")) is colored in green like a comment.
How can I deal with this problem
thanks

I got rid of the " after D100, and then I placed double " so VBA reads it as actual quotes in the formula. I hope this is what you were trying to accomplish.

oXLSheet.Cells(20, 2).Formula = "=SUMPRODUCT(COUNTIF(INDIRECT('Sheet1'!D2:D100),""*Economics*""))"

By the way, the code didn't comment anything out for me when I tried your original formula.

Hi
I am trying to write formulas from VB6 to an excel worksheet,the formula that i am trying to write is:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&J2&"'!L2:L1000"),"*Economics*")) but it considered ' as a comment so only =SUMPRODUCT(COUNTIF(INDIRECT( is considered as a formula.The remaining of the formula is colored in green like a comment.

May someone help me?
thanks

Hi

If I am not wrong then there should be a " sign after ...(INDIRECT(

so the code should actually be

VB Code:

oXLSheet.cells(20,2).Formula="=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet1'!D2 : D100"),"*Economics*"))"

plz correct me if i am wrong...

oops... i see what you mean... :blush:

i tried various combinations and i found out that when i was using oXLSheet.cells(20,2).Formula it gave me an error "object required"
however when i keyed in the below code it didn't give me any error but the cell value turned out to be "ref#!"

VB Code:

Worksheets("Sheet1").Cells(20, 2).Formula = "=SUMPRODUCT(COUNTIF(INDIRECT(Sheet1!$D$2:$D$100),"" * Economics * ""))"

would this help?

Code:

---

oXLSheet.cells(20,2).Formula="=SUMPRODUCT(COUNTIF(INDIRECT(" & chr(34) & "'Sheet1'!D2 : D100" & chr(34) & ")," & chr(34) & "*Economics*" & chr(34) & "))"

---

You need chr(34) the " speechmarks in the string itself. Either do two per speechmark ("") or escape the character or as I did above use the ascii/chr function.

You should have been worried when VBA coding changed colour! ;) intellisense to the rescue!

Quote:

---

Originally Posted by malik641

I got rid of the " after D100, and then I placed double " so VBA reads it as actual quotes in the formula. I hope this is what you were trying to accomplish.

oXLSheet.Cells(20, 2).Formula = "=SUMPRODUCT(COUNTIF(INDIRECT('Sheet1'!D2:D100),""*Economics*""))"

By the way, the code didn't comment anything out for me when I tried your original formula.

---

This worked just fine for me....did anyone else have a problem with it?

Even though I was using VBA, I tried with VB6 and got a good result.
I just made a command button on a blank userform:

VB Code:

Private Sub Command1_Click()
Dim oXLApp As Object
Dim oXLWB As Object
Dim oXLSheet As Object
 
Set oXLApp = GetObject(, "Excel.Application")
Set oXLWB = oXLApp.Workbooks.Add
Set oXLSheet = oXLWB.Sheets(1)
 
oXLSheet.Cells(20, 2).Formula = "=SUMPRODUCT(COUNTIF(INDIRECT('Sheet1'!D2:D100),""*Economics*""))"
 
Set oXLApp = Nothing
Set oXLWB = Nothing
Set oXLSheet = Nothing
 
Me.Hide
End Sub

Also, even though the "Code" tags are finding a comment in that, it didn't in VB6 or VBA.

Hi Ecniv

VB Code:

oXLSheet.cells(20,2).Formula="=SUMPRODUCT(COUNTIF(INDIRECT(" & chr(34) & "'Sheet1'!D2 : D100" & chr(34) & ")," & chr(34) & "*Economics*" & chr(34) & "))"

thanks a lot, ur code worked well.

No problem. Try out other peoples suggestions too - gives you more options when programming :)

Oh and if this thread is resolved, set it so via the tools link above the thread. :)

Happy programming