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]

You could just look for Equip in the filed, like so

=IF(ISNUMBER(SEARCH("equip",A1)),"EQUIP","")

or

=IF(ISNUMBER(FIND("Equip",A1)),"EQUIP","")

if it needs to be case sensitive.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Hi Bob,

Many thanks it worked. But imagine that I have 2000 rows and I have to copy
this formula in those rows which Ihave already did and it works. I was
thinking of a VBA function just to avoid increas in the size of the
documnets. Anyhow if there is no more alternative to this, this is still
great. and thanks once again.
Hoshyar

 

[Bob Phillips]

Hosyar,

Here is a macro

Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If InStr(1, LCase(Cells(i, "A").Value), "equip") > 0 Then
Cells(i, "B").Value = "EQUIP"
End If
Next i

End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Hi Bob,

Bear with me please, I am a begginer in this. I copied this macro and then
right click on the sheet tab> view code> paste. But it is not working. maybe
I need to do some changes in the macro? Because these codes appear in Column
A1:A2000 and I want to retrun "EQUIP" in Column K1:K2000.

Or maybe this marco interfaces with the other macro that I have on the same
sheet and it is run by a macro button.
This is the marco that I alreday have but it does other things
Sub Macro2()
'
' Macro1 Macro
' Macro recorded 06/01/2005 by Hoshyar Rassam
'
' Keyboard Shortcut: Ctrl+k

Sheets("Data Sheet").Visible = True
Sheets("entrysheet").Select
Range("A4:q2000").Select
Selection.Copy
Sheets("Data Sheet").Select
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A5"), Order1:=xlAscending, Key2:=Range("L5") _
, Order2:=xlAscending, Key3:=Range("m5"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Selection.Copy
Range("w5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("ah5"), Order1:=xlAscending,
Key2:=Range("ah5") _
, Order2:=xlAscending, Key3:=Range("ah5"), Order3:=xlAscending,
Header:= _
xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Selection.Copy
Range("As5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("ba5"), Order1:=xlAscending,
Key2:=Range("ba5") _
, Order2:=xlAscending, Key3:=Range("ba5"), Order3:=xlAscending,
Header _
:=xlNo, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Sheets("entrysheet").Select
Range("p52000").Select
Selection.Copy
Sheets("Data Sheet").Select
Range("av5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A15").Select
ActiveWindow.SelectedSheets.Visible = False
Range("A480").Select
End Sub

Please advise
Thanks for your patience
Hoshyar

 

[Guest]

Dear Bob,

I am an accountant working with a small company. I have built an excel
spread sheet and recoreded a very long marco as you see below, to achieve
what I need.

In fact I am getting lost here in this big spread sheet with my limited
knowledge on VBA and macro functions. Do you mind if you give me your email,
so that I can send the spread sheet to you and explain to you in details. I
am sure you can help me alot and make my life easier!!

Many thanks in advance and best regards
Hoshyar