VBA Countif Issue with variable formula - too many "??

[ddotson]

Hey everyone! I have come across an issue I am struggling with. I am looking at a sheet and counting the number of properties associated with a loan. On the sheet, you will have Loan, and if there are multiple properties that secure the Loan, they will be listed below. With that said, I am looping through a page and counting the number of Properties associated with the Loan. I am creating a beginning range variable = BegRange, and and ending range variable = EndRange. So, how this works is, if Line 1 is a Loan, and line, lets say Column B, 2 - 6 are properties, then it will bring back a BegRange of $b$2 and and EndRange of $b$6. Then, I will go back to the BegRange, offset to the row with Loan, and then offset to the column where I want the countif formula to reside. Here lies the problem, my code is not working as is for the CountIf Formula, it seems that I have too many "" in the formula, and it does not know where to stop?? I am not sure how to handle this, but I am sure one of you have seen this in the past, and have a solution. Thanks in advance!!



Do Until ActiveCell.Value = "END"



If ActiveCell.Value = "Property" Then
BegRange = ActiveCell.Address

Do Until ActiveCell.Value = "Loan"

ActiveCell.Offset(1, 0).Select
'
Loop

ActiveCell.Offset(-1, 0).Select
EndRange = ActiveCell.Address

Else

ActiveCell.Offset(0, -24).Select
ActiveCell.Value = "1"

End If


Range(BegRange).Select
ActiveCell.Offset(-1, 0).Select
ActiveCell.Offset(0, 24).Select
ActiveCell.Formula = "=COUNTIF(" & BegRange & ":" & EndRange & "), "Property")"

 

[Joe4]

Try this:

ActiveCell.Formula = "=COUNTIF(" & BegRange & ":" & EndRange & "," & Chr(34) & "Property" & Chr(34) & ")"

Note: Your code will run faster if you get rid of most of your ActiveCell and Select references. They usually are not necessary.
You can loop through the Range your need, or use For ... Next references will Cells(row,column) range references.

 

[ddotson]

Thanks Joe! I am not worried about the time, as the code runs in under a second..