VBA help to replace if formula

[Guest]

Hi All,

Column "A" is a category column, I always choos from a drop down list these
categories " Security Equip, IT Equip, HSE Equip, Comms Equip, Ops Equip" in
column "A".

Whenever these categories appears in column "A", I want to give them one
name for all of them in cluumn "K", such as "EQUIP". is it possible to do
that automatically?

currently I am using this formula in each cell in clumn "K"

=IF(A1="Office Equip","EQUIP",IF(A1="Comms. Equip","EQUIP",IF(A1="Security
Equip","EQUIP",IF(A1="Vehicles Equip","EQUIP",IF(A1="Ops
Equip","EQUIP",IF(A1="SHE Equip","EQUIP",IF(A1="IT Equip","EQUIP","")))))))

But this is a very long formla and it increases the size of my spread sheet.

Your help is appreciated
Best regards
Hoshyar

 

[Bob Phillips]

See other post.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

you don't say what you use if the selection doesn't end in Equip. However, to
get started...

three posibilities
(1) use a formula like
=IF(RIGHT(A1,5)="Equip", "Equip","Other")

or (2) use a UDF - makes modify and adding very simple
Function Category(source as string) as string
SELECT CASE TRUE
CASE RIGHT(source,5)="Equip"
Category = "Equip"
CASE ELSE
Category = "other"
END SELECT
End Function



:

 

[Guest]

Hej Patrick,

Many thanks. I am sorry that I have posted this thread twice, it was a
mistake. Bob has also been helping me. But to be honest with you both, As a
biginner to these function and VBA, I dont understand your second solution.
the fist one is ok but as I said I have 2000 cells under one columns to
return "Equip" in them. What I want is instead coyping this formula in 2000
cells, a VBA function that can do the job.

Therefore I like your second suggestion, but I really dont know how to do it
and where to copy that function, and remember that it not only one cell A1,
it is 2000 cells. So do I need to sellect a range A1:A2000?

If you can kindly explain to me step by step, just like you talking to a
biginner, you will make me the happiest man in the world at least for a few
hours.

Cheers
hoshyar