Excel Excel Multiple IF Statements

T

tahira

Joined
Aug 27, 2008
Messages
5
Reaction score
0
Does anyone know how to get around the
following Mulptiple 1F statements (9):

=IF((J2="Aberdeen"),"1",IF((J2="Joffre"),"2",IF((J2="Kegworth"),"3"
,IF((J2="Lyalta"),"4",IF((J2="Peace"),"5",
IF((J2="Rathwell"),"6",IF((J2="Tisdale"),"7",IF((J2="Virden"),"8",
IF((J2="Wilkie"),"9")))))))))

It keeps giving me an error after the 8th condition, is there anything I can do?
 
Excel does not like more than 7 nested functions. You can use the change event for the worksheet. Open your worksheet, hit Alt + F11 to edit VB. Enter the Worksheet_Change sub, and the Changer sub below. Save and close VB.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("J2:J2")) Is Nothing Then Exit Sub

Call Changer
End Sub

Sub Changer()
Select Case Range("J2").Value

Case "Aberdeen"
Range("J1").Value = 1
Case "Joffre"
Range("J1").Value = 2
Case "Kegworth"
Range("J1").Value = 3
Case "Lyalta"
Range("J1").Value = 4
Case "Peace"
Range("J1").Value = 5
Case "Rathwell"
Range("J1").Value = 6
Case "Tisdale"
Range("J1").Value = 7
Case "Virden"
Range("J1").Value = 8
Case "Wilkie"
Range("J1").Value = 9
Case Else
Range("J1").Value = ""
End Select

End Sub
 
Error

Hello I'm still having an error with this code! Can you help?


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("J2:J2")) Is Nothing Then Exit Sub

Call Changer
End Sub

Sub Changer()
Select Case Range("J2").Value

Case "Aberdeen"
Range("L2").Value = Louis Dryfus
Case "Joffre"
Range("L2").Value = Louis Dryfus
Case "Kegworth"
Range("L2").Value = Louis Dryfus
Case "Lyalta"
Range("L2").Value = Louis Dryfus
Case "Peace"
Range("L2").Value = Louis Dryfus
Case "Rathwell"
Range("L2").Value = Louis Dryfus
Case "Tisdale"
Range("L2").Value = Louis Dryfus
Case "Virden"
Range("L2").Value = Louis Dryfus
Case "Wilkie"
Range("L2").Value = Louis Dryfus
Case Else
Range("L2").Value = ""
End Select

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Dim dDate As Date
Private Sub TextBox1_Click()
Sheet1.Range("P:P").Value = dDate
End Sub

Private Sub TextBox1_AfterUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Mid(TextBox1.Value, 4, 2) > 12 Then
MsgBox "Invalid date, please re-enter", vbCritical
TextBox1.Value = vbNullString
TextBox1.SetFocus
Exit Sub
End If

dDate = DateSerial(Year(Date), Month(Date), Day(Date))
TextBox1.Value = Format(TextBox1.Value, "dd/mm/yyyy")
dDate = TextBox1.Value
End Sub
 
Great!...but do you know why the bottom part is not working?

Private Sub TextBox1_AfterUpdate(ByVal Cancel As MSForms.ReturnBoolean)

I keep receiving..user-defined type not defined error..

It doesn't seeme to work when i added the If interesct code above.
 
Second question, do you know how to change the cod so it works for the entire J column not just cell J2
 
I have had the same issue. I am currently having to work around by manually entering each cell because we needed to get the system up and running. Hoepfuly I will be able to figure this out before it turns into a giant snowballing mess.

-Conner
 
Here is a macro that runs down a column and performs the adjustment two columns over (from J2 to L2). Tu run it, create a new macro, select your starting row, and run the macro.

Sub CheckRows()
' NOTE: You must select the first cell in the column
'The macro ends when it reaches a blank cell
ScreenUpdating = False
FirstItem = ActiveCell.Value
SecondItem = "Louis Dryfus"

offsetcount = 0
FirstItem = ActiveCell.Offset(offsetcount, 0).Value
Do While FirstItem <> ""

Select Case FirstItem

Case "Aberdeen"
ActiveCell.Offset(offsetcount, 2).Value = SecondItem
Case "Joffre"
ActiveCell.Offset(offsetcount, 2).Value = SecondItem
Case "Kegworth"
ActiveCell.Offset(offsetcount, 2).Value = SecondItem
Case "Lyalta"
ActiveCell.Offset(offsetcount, 2).Value = SecondItem
Case "Peace"
ActiveCell.Offset(offsetcount, 2).Value = SecondItem
Case "Rathwell"
ActiveCell.Offset(offsetcount, 2).Value = SecondItem
Case "Tisdale"
ActiveCell.Offset(offsetcount, 2).Value = SecondItem
Case "Virden"
ActiveCell.Offset(offsetcount, 2).Value = SecondItem
Case "Wilkie"
ActiveCell.Offset(offsetcount, 2).Value = SecondItem
Case Else
ActiveCell.Offset(offsetcount, 2).Value = ""
End Select


offsetcount = offsetcount + 1
FirstItem = ActiveCell.Offset(offsetcount, 0).Value
Loop
ScreenUpdating = True

End Sub
 
Hi Stoneboysteve,
I got a trouble to use find and replace by macro. More specifically, I want to replace the wind directions, stand by N, S, E,W, NE, NW, SE,SW,NNE, ESE... (16 directions) by equivalent numbers. For example: I want to replace N by 36, S by 18, E by 9, or W by 27.... Since the limitation of the IF function, maximum 7 loops, I am thinking of creating a macro for this purpose.
The wind directions data is structured by the range B4: R5000 in one sheet. What I want to do is a conversion from characters in sheet 1 to numbers in sheet 2, with keeping the same relative positions of data, i.e.: Character in Sheet1!B4 ----> convert to a Number in Sheet2!B4.
I hope you can understand my descriptions on the data structure as well as my target.
Help me, Mr. Master of Excel macro.
 
great, i have same trouble to, but now i can solve it..
cool2.gif
 
Back
Top