[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Expressing a pretty complex formula in VBA with string variables

Posted on 2011-05-04
8
Medium Priority
?
343 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:gabrielPennyback
8 Comments
 
LVL 35

Expert Comment

by:Norie
ID: 35694600
Do you mean you want to incorporate the If part in the actual formula?

Or do you want to use If the code?
0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 35695038
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.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35695050
I think you cannot do it. You will have to use the line 1 of your code
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 81

Accepted Solution

by:
byundt earned 2000 total points
ID: 35700596
John,
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"
Next

End Sub

Open in new window

0
 
LVL 1

Author Closing Comment

by:gabrielPennyback
ID: 35703345
Thanks, Brad, I'm sure that this will do it.

- John
0
 
LVL 81

Expert Comment

by:byundt
ID: 35703397
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
0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 35709836
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: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27021735.html#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

0
 
LVL 81

Expert Comment

by:byundt
ID: 35710342
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
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question