Macro to isolate a 3rd party add-in group of functions.

[Risk]

I've got this nifty macro that I use to isolate a specific 3rd party add-in group of functions so that they can be valued without other stand XL functions being valued, but I'm very new to VB and I don't know how to make a modification to it. The limitation of this function is it doing a IF left 3 spaces "=HP" then value cell, but many times I have cells which contain the "HP" functions and they are not at the beginning of the code. e.g. =IF((HPVAL(X,X,X,X,X)>1),TRUE,FALSE)

In this example, the cell would not be copy paste valued since the left three spaces are "=IF" and not "=HP". The code I presently have is at the bottom of this message

....would the proper correction be


For Each z In Selection
If CONTAINS(z.FormulaR1C1) <> "HPLNK" Then
If CONTAINS(z.FormulaR1C1) = "HP" Then




Thanks for your help,

Risk



***** ORIGINAL ******

Sub ValueHPAll()
TxtMsg = "You have selected to value all Hyperion formula's in this workbook. If you wish to proceed please press OK"
y = MsgBox(TxtMsg, vbOKCancel, "Proceeding with valuing Hyperion formula's.")

If y = 1 Then

For Each x In Worksheets
Sheets(x.Name).Activate
Range("a1").Select
ActiveCell.SpecialCells(xlLastCell).Select
LastCell = ActiveCell.Address
Range("a1:" & LastCell).Select

For Each z In Selection
If Left(z.FormulaR1C1, 7) <> "=HPLNK" Then
If Left(z.FormulaR1C1, 3) = "=HP" Then
z.Copy
z.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
End If
Next z
Range("a1").Select
Next x


Else
MsgBox "You have chosen to cancel this process"

End If

End Sub

 

[sbendbuckeye]

If you are just getting started, save this stuff for later because most of the time you don't need it. InStr just returns the postion within one string where if found the second string. So Instr("abcdefg", "cd") = 3 because that is where the string "cd" is found within "abcdefg".

If I understand your post, you are looking for a group of functions that all begin with HP, except for HPLnk which is excluded. My RhPass example above was a made up name to point out that if you had functions with an HP embedded within their name that they may erroneously match if all you were using was Instr.

You will probably be OK using the solutions previously posted. If you like to dig into ugly details then here are some Regular Expression links for you:
http://regexadvice.com/
http://regexlib.com/CheatSheet.aspx
http://regexlib.com/Default.aspx
http://www.regular-expressions.info/dotnet.html
http://www.dotnetforums.net/forumdisplay.php?f=74

If this really intrigues you, check out www.RegExBuddy.com, a wonderful tool for $30 US. Feel free to post again if you have more questions. Good Luck!

 

[Risk]

Is it?

If InStr(1, z.FormulaR1C1, "HPLNK") = 0 Then
If InStr(1, z.FormulaR1C1, "HP") > 0 Then

Andrew thank you for your help! This did the trick.