Value assignment in a formula

  • Thread starter Thread starter Kebbon
  • Start date Start date
K

Kebbon

I recently had to compose a formula to determine whether the contents of
cell had either a DB or CR suffix (it was imported as text strings)

After a protracted struggle I came up with the following:
IF(ISERROR(SEARCH("CR",B46)),LEFT(B46,(SEARCH("DB",B46)-1)),(LEFT(B46,(SEARC
H("CR",B46)-1)*-1))

While I would welcome any refinements, I am particularly interested in
whether I actually needed to constantly repeat the SEARCH function. Could I
have performed the search of CR, for example and stored that result in a
variable that I could have recalled when needed, saving both typing time and
computer cycles?
 
Hi
for storing a variable have a look at
http://makeashorterlink.com/?J32E23767
(option 2+3 -> the latter one reposted below)
-----
Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function
 
Thanks Frank. I have only been a subscriber for a couple of hours to this
group and I can already attest to your generosity and expertise. Again,
thanks for sharing.
Cheers,
Kebbon
 
Kebbon
This doesn't really save you much but you can avoid the SEARCH function completely

=(IF(RIGHT(B46,2)="CR",-LEFT(B46,LEN(B46)-2),--LEFT(B46,LEN(B46)-2))

The -- in front of the false conditions forces the return to be numeric

You might want to use a User Defined Finction if you need this frequently

-----------------------------------------------------------------
Public Function TRANS(transaction
' Strips number from data and makes credit negativ

If Right(transaction, 2) = "cr" Or
Right(transaction, 2) = "CR" The

mult = -
Els
mult =
End I

transamount = Left(transaction, Len(transaction) - 2

TRANS = transamount * mul

End Functio
-----------------------------------------------------------------

If you enter this in a VB module you can use the formula =TRANS(B46) in your worksheet

Good Luck
Mark Graesse
(e-mail address removed)


----- Kebbon wrote: ----

I recently had to compose a formula to determine whether the contents o
cell had either a DB or CR suffix (it was imported as text strings

After a protracted struggle I came up with the following
IF(ISERROR(SEARCH("CR",B46)),LEFT(B46,(SEARCH("DB",B46)-1)),(LEFT(B46,(SEAR
H("CR",B46)-1)*-1)

While I would welcome any refinements, I am particularly interested i
whether I actually needed to constantly repeat the SEARCH function. Could
have performed the search of CR, for example and stored that result in
variable that I could have recalled when needed, saving both typing time an
computer cycles
 
Back
Top