0

I'd like to insert a count search function into a cell and make it dynamic using VBA.

enter image description here

In the cell G2 I want to insert the following formula: =COUNT(SEARCH(($F$2:$F$3;B2))=0 so that it returns a true or false value. As the Table in cell A1 and the column F are liable to change I need to create a code that would insert a dynamic count search function in there.

This is the code I've inserted and I've tried to highlight the code from cell F2 and below but it doesn't seem to work.

ActiveCell.Formula2R1C1 = "=COUNT(SEARCH(Range."F2".Currentregion,RC[-5]))=0"

Anyone know how I can edit the code so that it looks looks up the region below F2 dynamically and creates an absolute value and also creqtes B2 a relative reference (appears to be currently RC[-5] in the code ?

Thanks,

braX
  • 11,506
  • 5
  • 20
  • 33
Bikat Uprety
  • 139
  • 8
  • You're conflating VBA and formulas. Something like `Range("F2").CurrentRegion.Address(ReferenceStyle:=xlR1C1)` should be concatenated with the formula string, using `&`. – BigBen Feb 25 '22 at 17:06
  • I am completely lost on what you are asking for. Maybe a good idea to describe what you end result should look like based on the data you have given? – Aldert Feb 25 '22 at 18:28
  • Could you share what the results in `G2` and `G3` should be and how they are calculated? Please do describe it in detail because I don't understand what a *count/search formula* is. – VBasic2008 Feb 25 '22 at 20:43

0 Answers0