COUNTIF in an Excel macro-CodePudding

Help, I need to enter the following formula into an Excel spreadsheet. If I enter this exact line of code into a macro I get a runtime error 1004 application-defined or object-defined error. If I remove the “s around the “>500000000” and “>750000000” the macro error stops but then the COUNTIF in the spreadsheet gives an error. Can anyone offer me solution? Thank you.

ActiveSheet.Cells(Row2, Col2   2).Formula = "=COUNTIF('Share Price Data Gain Trans'!U" & Row3 & ":U" & Row1 - 1 & ",">500000000")-COUNTIF('Share Price Data Gain Trans'!U" & Row3 & ":U" & Row1 - 1 & ",">750000000")"

CodePudding user response：

You need to double tho quotes around >500000000 because quotes inside a string need to be doubled.

ActiveSheet.Cells(Row2, Col2   2).Formula = "=COUNTIF('Share Price Data Gain Trans'!U" & Row3 & ":U" & Row1 - 1 & ","">500000000"")-COUNTIF('Share Price Data Gain Trans'!U" & Row3 & ":U" & Row1 - 1 & ","">750000000"")"

CodePudding user response：

try a few more & and " or some extra "'s "" xxxx ""

 & ","         &"          >500000000")-COUNTIF('Share Price Data Gain Trans'!U" & Row3 & ":U" & Row1 - 1 & ","         & "       >750000000")"