I have a couple of formulas that I would like to replace with a sub. This is beyond my capabilities however. Any suggestions are appreciated. The formulas follow

=IF(ISBLANK(B22),"",IF(ISBLANK(B23),"",IF((B22-B23=0),"Met",IF((B22-B23<0),"Over","Under")))

=IF(ISBLANK(B22),"",IF(ISBLANK(B23),"",IF((B22-B23<0),-(B22-B23),IF((B22-B23=0),"",B22-B23))))

'=IF(ISBLANK(B22),"",IF(ISBLANK(B23),"",IF((B22-B23=0),"Met",IF((B22-B23<0),
"Over","Under"))))
Sub test()
Dim rng1 As Range, rng2 As Range

Set rng1 = Range("B22")
Set rng2 = Range("B23")

With ActiveCell
If IsEmpty(rng1.Value) Or IsEmpty(rng2.Value) Then
.Value = ""
Else
Select Case rng1.Value - rng2.Value
Case 0: .Value = "Met"
Case Is < 0: .Value = "Over"
Case Else: .Value = "Under"
End Select
End If
End With
End Sub

'=IF(ISBLANK(B22),"",IF(ISBLANK(B23),"",IF((B22-B23<0),-(B22-B23),IF((B22-B2
3=0),"",B22-B23))))
Sub test2()
Dim rng1 As Range, rng2 As Range, dblTemp As Double

Set rng1 = Range("B22")
Set rng2 = Range("B23")

With ActiveCell
If IsEmpty(rng1.Value) Or IsEmpty(rng2.Value) Then
.Value = ""
Else
dblTemp = rng1.Value - rng2.Value
Select Case dblTemp
Case Is < 0: .Value = -dblTemp
Case 0: .Value = ""
Case Else: .Value = dblTemp
End Select
End If
End With
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Erik" wrote in message
...
I have a couple of formulas that I would like to replace with a sub. This
is beyond my capabilities however. Any suggestions are appreciated. The
formulas follow:


=IF(ISBLANK(B22),"",IF(ISBLANK(B23),"",IF((B22-B23=0),"Met",IF((B22-B23<0),"
Over","Under"))))


=IF(ISBLANK(B22),"",IF(ISBLANK(B23),"",IF((B22-B23<0),-(B22-B23),IF((B22-B23
=0),"",B22-B23))))

Erik,

Why would you want to replace worksheet formulae with VBA? It's more work,
less efficient, ....

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Erik" wrote in message
...
I have a couple of formulas that I would like to replace with a sub. This
is beyond my capabilities however. Any suggestions are appreciated. The
formulas follow:


=IF(ISBLANK(B22),"",IF(ISBLANK(B23),"",IF((B22-B23=0),"Met",IF((B22-B23<0),"
Over","Under"))))


=IF(ISBLANK(B22),"",IF(ISBLANK(B23),"",IF((B22-B23<0),-(B22-B23),IF((B22-B23
=0),"",B22-B23))))

Hi Bob
I put the formula in because it's the only way I know to do things. I agree that it's far less work. However, I tested the operability of the sheet with my wife and the first thing she did was delete the formula from a handfull of the cells (then asked why my spreadsheet wasn't working). I can't protect the sheet without conflicting with the code I'm using to format the cell color. So, although more work for me, I decided that using a sub to acomplish the task was somewhat more idiot proof

Erik

Erik,

Not trying to labour or be awkward, but just help you get the best result,
but if she can delete a formula, shoe can just as easily delete a result.
What is your protection then.

If your wife or anyone else is likely to delete your good work, it seems to
me that locking it down is a better solution.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Erik" wrote in message
...
Hi Bob,
I put the formula in because it's the only way I know to do things. I
agree that it's far less work. However, I tested the operability of the
sheet with my wife and the first thing she did was delete the formula from a
handfull of the cells (then asked why my spreadsheet wasn't working). I
can't protect the sheet without conflicting with the code I'm using to
format the cell color. So, although more work for me, I decided that using
a sub to acomplish the task was somewhat more idiot proof.

Erik

Bob
Again, I agree. Locking the sheet would be the best answer. However, I tried that with sheet protection and it interfered with the Sub Worksheet_Change code I have formatting the cell color elsewhere in the sheet. I figured that deleting some results with a sub providing it, the operator would only need reinput the values for the sub to provide the answer again. Whereas if the same person were to delete the formula, he or she would then have to figure the answer and enter it manually. If there is a way to lock those cells with formulae to prevent deletion without affecting the rest of the sheet, I'm all for it. But I was not able to get it to work that way. As always, suggestions greatly appreciated
Erik