Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > VBA Help to replace If formula
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 25
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default 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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 25
Default 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




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default 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






Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 25
Default 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







Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 25
Default 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







Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
If formula to replace the #N/A value Max Excel Worksheet Functions 2 August 17th 07 02:07 AM
Formula to Replace or eliminate any sheetname(s) in formula string EagleOne Excel Discussion (Misc queries) 0 September 20th 06 06:36 PM
Formula to replace nos. below 100?? [email protected] Excel Discussion (Misc queries) 3 December 3rd 05 08:24 PM
rounding a formula in a cell, then replace the formula Don Guillett[_4_] Excel Programming 0 July 13th 04 03:59 PM
rounding a formula in a cell, then replace the formula Frank Kabel Excel Programming 0 July 13th 04 03:52 PM


All times are GMT +1. The time now is 05:13 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement