Good day to all. This is my first time asking a question here so please excuse any etiquette mistakes. I have searched for an answer to my problem but drawn a blank so far, but perhaps I'm asking the wrong question. My VBA knowledge is self-taught and I'm far from a pro.
I've developed a couple of functions that will compare an input range of text and/or numbers with text token and return a range of true/false answers, so that I can then use that array in array formulas and so on. The reason is to develop a way of dealing with a problem I have later, this is just a dev project until I can get it working.
My code is:
Function EvalStr(Inp As String) As Integer
Dim result As Boolean
result = Evaluate(Inp)
If result Then
EvalStr = 1
EvalStr = 0
Function EvalRng(TestStr As String, InpRng As Range, Optional ForceHigh As Boolean) As Double()
Dim OutRng() As Double
Dim RngSize As Long
Dim ThisStr As String
Dim i As Integer
RngSize = InpRng.Cells.Count
ReDim OutRng(RngSize - 1) As Double
If RngSize > 0 Then
For i = 1 To RngSize
ThisStr = "=" & InpRng(i).Value2 & TestStr
OutRng(i - 1) = EvalStr(ThisStr)
EvalRng = OutRng
My spreadsheet is attached as an image so you can see what is going on.
A2 is just >=6 entered as text.
C2:C11 is a series of incremental numbers, the source data.
D2:D11 are some manually entered zeroes and ones, a baseline for the logic of A2.
F2:F11 is =EvalStr(C2&$A$2)
G2 is =SUMPRODUCT(EvalRng($A$2,C2:C11),C2:C11)
D15:F15 is =SUMPRODUCT($C2:$C11,D2:D11)
Ignore column E
D15 is the control sample. F15 also works, so my Functions are half right. G2 does not, and I'm stumped as to why, I've reached the limit of my knowledge.
When I use the Formula Entry dialog to work out why it's not working, my array result from EvalRng is separated with commas, whereas the other range is separated with semi-colons. So I'm guessing my EvalRng is the 'wrong' kind of array...? How do I fix that?
The dialog box clue
Many thanks for any tips or assistance.