[RESOLVED] problem with ' in a formula

[engnouna]

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

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

[engnouna]

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

[Siddharth Rout]

Hi

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

so the code should actually be

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

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#!"

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

would this help?

[Ecniv]

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!

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

[malik641]

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:

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.

[engnouna]

Hi Ecniv

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.

[Ecniv]

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