Is this syntax correct?

[joycesolomon]

Range("Actual!B4").Formula = "=SUM(QueryA!$C$2:$C$" & lrC & " , "=Completed")"

 

[VoG]

Try

Sheets("Actual").Range("B4").Formula = "=SUM(QueryA!$C$2:$C$" & lrC & ",""Completed"")"

 

[joycesolomon]

hi

I tried the given syntax, and i dont see anything generated...could you please help?

 

[VoG]

It definitely generates something but I don't think what you want. I only looked at the syntax, not what the formula was doing. Perhaps it should be

Sheets("Actual").Range("B4").Formula = "=COUNTIF(QueryA!$C$2:$C$" & lrC & ",""Completed"")"

If not, what should the formula be calculating?

 

[joycesolomon]

Hi,

Here is my worksheet example:


Column A

Not Started
Not Started
Not Started
Not Started
Not Started
Completed
Not Started
Not Started
Not Started
Completed


Column B

<TABLE style="WIDTH: 134pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=179><COLGROUP><COL style="WIDTH: 134pt; mso-width-source: userset; mso-width-alt: 6546" width=179><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 134pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl63 height=20 width=179 align=right>8/26/2011
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl63 height=20 align=right>9/2/2011
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl63 height=20 align=right>9/2/2011
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl63 height=20 align=right>9/2/2011
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl63 height=20 align=right>8/19/2011
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl63 height=20 align=right>8/19/2011
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl63 height=20 align=right>9/13/2011
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl63 height=20 align=right>9/7/2011
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl63 height=20 align=right>8/26/2011
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl63 height=20 align=right>9/9/2011
</TD></TR></TBODY></TABLE>


I just want to be able to track the total rows that exist, and also in that total how many is Completed

I really appreciate any help you can extend!

 

[VoG]

So if you want to count the Completeds in Column A it would be like this

Sheets("Actual").Range("B4").Formula = "=COUNTIF(QueryA!$A$2:$C$" & lrC & ",""Completed"")"

 

[joycesolomon]

is there a way i can send you my file, maybe i am not seeing the error that i am causing, but i still dont see a value generated....my coding might be messy

 

[VoG]

Try running this : you should see something in B4

Sub test()
Dim lrC As Long
lrC = 4
Sheets("Actual").Range("B4").Formula = "=COUNTIF(QueryA!$A$2:$A$" & lrC & ",""Completed"")"
End Sub