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
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