asked on

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?

Thanks,
John

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

Norie

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

Or do you want to use If the code?

John Carney

ASKER

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?

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

I don't know if you can do that.

Saqib Husain

I think you cannot do it. You will have to use the line 1 of your code

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Carney

ASKER

Thanks, Brad, I'm sure that this will do it.

- John

byundt

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!

Brad

John Carney

ASKER

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: https://www.experts-exchange.com/questions/27021735/Targeting-occurences-of-two-non-contiguous-words-with-InStr.html?anchorAnswerId=35708345#a35708345

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

John

Sub Code_INOP()
Dim cel As Range, j As Range
For Each cel In [All_Incidents]
cel.Select
    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"
      Next
If InStr(UCase(cel), "WORKING") > 0 And InStr(UCase(cel), "NO ") > 0 Or InStr(UCase(cel), "NOTHING") > 0 Then j = "INOP"
Next
End Sub

Open in new window

byundt

John,
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"

Brad