VBA Help to replace If formula
 

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

VBA Help to replace If formula
 

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)

"Hoshyar" wrote in message
...
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

VBA Help to replace If formula
 

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" wrote:

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)

"Hoshyar" wrote in message
...
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

VBA Help to replace If formula
 

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)

"Hoshyar" wrote in message
...
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" wrote:

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)

"Hoshyar" wrote in message
...
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

VBA Help to replace If formula
 

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("p5:p2000").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

"Bob Phillips" wrote:

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)

"Hoshyar" wrote in message
...
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" wrote:

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)

"Hoshyar" wrote in message
...
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

VBA Help to replace If formula
 

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


"Hoshyar" wrote:

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("p5:p2000").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

"Bob Phillips" wrote:

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)

"Hoshyar" wrote in message
...
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" wrote:

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)

"Hoshyar" wrote in message
...
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