Expressing a pretty complex formula in VBA with string variables

I have about 50 lines of code in a macro and they're all identical except for a 3-letter code which occurs twice ("AOD" in this case). I was hoping I could write a couple of variables to do the job but I just can't seem to get the strings right. I can produce the exact line of code that I want with the second code below which produces this value in [AJ7]:
x.Worksheet.Evaluate("COUNT(MATCH(""*""&KWDescr_AOD&""*"",$T$6,0))") > 0 Then cel = "AOD"

But how do I adapt that to work within an "IF" statement? Is it even possible to do  this kind of thing with strings?

If x.Worksheet.Evaluate("COUNT(MATCH(""*""&KWDescr_AOD&""*""," & x.Address & ",0))") > 0 Then cel = "AOD"

- - - - - - - - - - - - - - - - - - - - - - - - 
str1 = "x.Worksheet.Evaluate(""COUNT(MATCH(""""*""""&KWDescr_"
str2 = "&""""*""""," & x.Address & ",0))"
str3 = """) > 0 Then cel = "
str4 = """"
str5 = "If "

[AJ7] = str1 & "AOD" & str2 & str3 & str4 & "AOD" & str4

Open in new window

John CarneyReliability Business Tools Analyst IIAsked:
Who is Participating?
byundtConnect With a Mentor Commented:
If I understand your goal, you have a VBA variable KWDescr_AOD that you want to substitute into the Evaluate expression. Unfortunately, you cannot build the name of the VBA variable as a string expression. But you could repurpose your code like this:
Sub FormulaVariables()
Dim vShort As Variant, vLong As Variant
Dim i As Long, n As Long
Dim frmla As String
Dim cel As Range, x As Range
Dim KWDescr_AOD, KWDescr_BCD, KWDescr_DEF    'I don't know what type of variables these ought to be
vShort = Array("AOD", "BCD", "DEF")
vLong = Array(KWDescr_AOD, KWDescr_BCD, KWDescr_DEF)
Set cel = ActiveCell
Set x = [A1]
n = UBound(vShort)
For i = 0 To n
    frmla = "COUNT(MATCH(""*"" & " & vLong(i) & " & ""*""," & x.Address & ",0))"
    If x.Worksheet.Evaluate(frmla) > 0 Then cel = "AOD"

End Sub

Open in new window

NorieVBA ExpertCommented:
Do you mean you want to incorporate the If part in the actual formula?

Or do you want to use If the code?
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Either way. If the "IF" functionality will work when it's included in the string, great. But I found that even though my "string" of strings will produce in a cell exactly what I want the line of code to be, VBA seems to not know what to do with a standalone string.
When I put this: str1 & "AOD" & str2 & str3 & str4 & "AOD" & str4  ... on a line by itself, VBA won't take it.

But is it possible to combine action words like 'If" and "Then" and strings in one line?

str1 & "AOD" & str2 & str3
Then cel = "AOD"

I don't know if you can do that.
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Saqib Husain, SyedEngineerCommented:
I think you cannot do it. You will have to use the line 1 of your code
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Thanks, Brad, I'm sure that this will do it.

- John
Due to work pressures, I've been answering fewer questions the past six months.

As a result, I can see pretty significant increase in the difficulty of questions that you are asking. I think that means that you are getting more savvy with the VBA--so congratulations!

John CarneyReliability Business Tools Analyst IIAuthor Commented:
Thank you Brad, that really means a lot coming from you. I think I'm starting to get the hang of it. I know I love doing it.

I actually came up with an alternative way of approaching this whole problem which I really like a lot but it leaves me with the same problem as this approach, which is accomodating split strings. That is, one of the strings in [KWDescr_AOD] is "not working", but sometimes the text being parsedcontains something like "not always working" and I want to be able to pick up on that as well. Without resorting to hard-coded exceptions like the one I have below.

If you have the time, please take alook at the question:

In any event, thanks agin and have a great weekend ... if work pressures allow :-)

Sub Code_INOP()
Dim cel As Range, j As Range
For Each cel In [All_Incidents]
    Set j = cel.Offset(0, 8)
    Dim i As Long
      For i = [KW_INOP].Row To [KW_INOP].End(xlDown).Row
      If InStr(UCase(cel), Cells((i), [KW_INOP].Column)) > 0 Then j = "INOP"
If InStr(UCase(cel), "WORKING") > 0 And InStr(UCase(cel), "NO ") > 0 Or InStr(UCase(cel), "NOTHING") > 0 Then j = "INOP"
End Sub

Open in new window

You may find the Like operator to be worth learning about:

If UCase(cel.Value) Like "* NOT *WORKING" Then j = "INOP"       'The * is a wild card, and stands for 0 or more characters. Traps "not working" and "not always working"

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.