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

ddotson

Board Regular
Joined
Nov 9, 2004
Messages
57
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")"
 
Last edited:

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,882
Office Version
  1. 365
Platform
  1. Windows
Try this:
Code:
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

Board Regular
Joined
Nov 9, 2004
Messages
57
Thanks Joe! I am not worried about the time, as the code runs in under a second..
 

Watch MrExcel Video

Forum statistics

Threads
1,122,217
Messages
5,594,887
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top