| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If, Else statements
Hi
Can anyone please help me? I get an "Else without If" error when running this and not sure why as it seems the same as the example in the help file. Also, what is the best way to run it if I want to run this when a certain cell is changed. Or can it only be done when the worrksheet is changed. Many thanks Steve R If Cells.Cells(9, 3).Value = "" Then Cells.Cells(9, 8).Value = "" ElseIf Cells.Cells(9, 7).Value = "N/A" Then Cells.Cells(9, 8).Value = "0" ' Category S ElseIf Cells.Cells(9, 7) = "S" & Cells.Cells(9, 6).Value < 5 Then Cells.Cells(9, 8).Value = "0" ElseIf Cells.Cells(9, 7) = "S" & Cells.Cells(9, 6) < 10 Then Cells.Cells(9, 8).Value = "5%" ElseIf Cells.Cells(9, 7) = "S" & Cells.Cells(9, 6) 9 Then Cells.Cells(9, 8).Value = "10%" ' Category P ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) < 3 Then Cells.Cells(9, 8).Value = "0" ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) < 5 Then Cells.Cells(9, 8).Value = "5%" ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) < 10 Then Cells.Cells(9, 8).Value = "15%" ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) 10 Then Cells.Cells(9, 8).Value = "20%" Else Cells.Cells(9, 8).Value = "" End If -- Steve R |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If, Else statements
Steve,
If you put the action on the same line as the IF, there is no EndIf required. So If a = b Then do_something should be replaced by If a = b Then do_something In total If Cells.Cells(9, 3).Value = "" Then Cells.Cells(9, 8).Value = "" ElseIf Cells.Cells(9, 7).Value = "N/A" Then Cells.Cells(9, 8).Value = "0" ' Category S ElseIf Cells.Cells(9, 7) = "S" & Cells.Cells(9, 6).Value < 5 Then Cells.Cells(9, 8).Value = "0" ElseIf Cells.Cells(9, 7) = "S" & Cells.Cells(9, 6) < 10 Then Cells.Cells(9, 8).Value = "5%" ElseIf Cells.Cells(9, 7) = "S" & Cells.Cells(9, 6) 9 Then Cells.Cells(9, 8).Value = "10%" ' Category P ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) < 3 Then Cells.Cells(9, 8).Value = "0" ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) < 5 Then Cells.Cells(9, 8).Value = "5%" ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) < 10 Then Cells.Cells(9, 8).Value = "15%" ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) 10 Then Cells.Cells(9, 8).Value = "20%" Else Cells.Cells(9, 8).Value = "" End If -- HTH RP (remove nothere from the email address if mailing direct) "Steve" wrote in message ... Hi Can anyone please help me? I get an "Else without If" error when running this and not sure why as it seems the same as the example in the help file. Also, what is the best way to run it if I want to run this when a certain cell is changed. Or can it only be done when the worrksheet is changed. Many thanks Steve R If Cells.Cells(9, 3).Value = "" Then Cells.Cells(9, 8).Value = "" ElseIf Cells.Cells(9, 7).Value = "N/A" Then Cells.Cells(9, 8).Value = "0" ' Category S ElseIf Cells.Cells(9, 7) = "S" & Cells.Cells(9, 6).Value < 5 Then Cells.Cells(9, 8).Value = "0" ElseIf Cells.Cells(9, 7) = "S" & Cells.Cells(9, 6) < 10 Then Cells.Cells(9, 8).Value = "5%" ElseIf Cells.Cells(9, 7) = "S" & Cells.Cells(9, 6) 9 Then Cells.Cells(9, 8).Value = "10%" ' Category P ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) < 3 Then Cells.Cells(9, 8).Value = "0" ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) < 5 Then Cells.Cells(9, 8).Value = "5%" ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) < 10 Then Cells.Cells(9, 8).Value = "15%" ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) 10 Then Cells.Cells(9, 8).Value = "20%" Else Cells.Cells(9, 8).Value = "" End If -- Steve R |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If, Else statements
Couple of things...
First, the VBA compiler views a If...Then... on a single line as a complete statement. You need to typographically break it into two lines to make the following line part of the if...then...elsif... structu If Cells(9, 3).Value = "" Then Cells(9, 8).Value = "" ElseIf Cells(9, 7).Value = "N/A" Then Cells(9, 8).Value = 0 End If You're also using & improperly - & is the concatenation operator, so If Cells(9, 7) = "P" & Cells(9, 6) < 3 Then is executed (assuming both conditions are true), as If "TrueTrue" Then You could try something like: If IsEmpty(Cells(9, 3).Value) Then Cells(9, 8).ClearContents Else Select Case Cells(9, 7).Value Case "N/A" Cells(9, 8).Value = 0 Case "S" Cells(9, 8).Value = Application.Min(0.1, _ Int(Cells(9, 6).Value / 5) / 20) Case "P" Cells(9, 8).Value = -0.05 * ((Cells(9, 6).Value = 3) + _ 2 * (Cells(9, 6).Value = 5) + (Cells(9, 6).Value = 10)) Case Else Cells(9, 8).ClearContents End Select End If In article , "Steve" wrote: Hi Can anyone please help me? I get an "Else without If" error when running this and not sure why as it seems the same as the example in the help file. Also, what is the best way to run it if I want to run this when a certain cell is changed. Or can it only be done when the worrksheet is changed. Many thanks Steve R If Cells.Cells(9, 3).Value = "" Then Cells.Cells(9, 8).Value = "" ElseIf Cells.Cells(9, 7).Value = "N/A" Then Cells.Cells(9, 8).Value = "0" ' Category S ElseIf Cells.Cells(9, 7) = "S" & Cells.Cells(9, 6).Value < 5 Then Cells.Cells(9, 8).Value = "0" ElseIf Cells.Cells(9, 7) = "S" & Cells.Cells(9, 6) < 10 Then Cells.Cells(9, 8).Value = "5%" ElseIf Cells.Cells(9, 7) = "S" & Cells.Cells(9, 6) 9 Then Cells.Cells(9, 8).Value = "10%" ' Category P ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) < 3 Then Cells.Cells(9, 8).Value = "0" ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) < 5 Then Cells.Cells(9, 8).Value = "5%" ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) < 10 Then Cells.Cells(9, 8).Value = "15%" ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) 10 Then Cells.Cells(9, 8).Value = "20%" Else Cells.Cells(9, 8).Value = "" End If |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If, Else statements
Thanks Bob
at least I don't get the error now. I need to track the changes in particular cells when running this code. at the moment I have used the: " Private Sub Worksheet_Change(ByVal Target As Range)" option but this does not change the values either when i update the cells. Any ideas? I appreciate you help. Regards Steve R "Steve" wrote: Hi Can anyone please help me? I get an "Else without If" error when running this and not sure why as it seems the same as the example in the help file. Also, what is the best way to run it if I want to run this when a certain cell is changed. Or can it only be done when the worrksheet is changed. Many thanks Steve R If Cells.Cells(9, 3).Value = "" Then Cells.Cells(9, 8).Value = "" ElseIf Cells.Cells(9, 7).Value = "N/A" Then Cells.Cells(9, 8).Value = "0" ' Category S ElseIf Cells.Cells(9, 7) = "S" & Cells.Cells(9, 6).Value < 5 Then Cells.Cells(9, 8).Value = "0" ElseIf Cells.Cells(9, 7) = "S" & Cells.Cells(9, 6) < 10 Then Cells.Cells(9, 8).Value = "5%" ElseIf Cells.Cells(9, 7) = "S" & Cells.Cells(9, 6) 9 Then Cells.Cells(9, 8).Value = "10%" ' Category P ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) < 3 Then Cells.Cells(9, 8).Value = "0" ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) < 5 Then Cells.Cells(9, 8).Value = "5%" ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) < 10 Then Cells.Cells(9, 8).Value = "15%" ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) 10 Then Cells.Cells(9, 8).Value = "20%" Else Cells.Cells(9, 8).Value = "" End If -- Steve R |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If, Else statements
Many thanks, I will give that a go.
Can I get the VB to run only if a certain field is changed? If not, what is best Worksheet_calculate or Worksheet_change? Thanks Steve "Steve" wrote: Thanks Bob at least I don't get the error now. I need to track the changes in particular cells when running this code. at the moment I have used the: " Private Sub Worksheet_Change(ByVal Target As Range)" option but this does not change the values either when i update the cells. Any ideas? I appreciate you help. Regards Steve R "Steve" wrote: Hi Can anyone please help me? I get an "Else without If" error when running this and not sure why as it seems the same as the example in the help file. Also, what is the best way to run it if I want to run this when a certain cell is changed. Or can it only be done when the worrksheet is changed. Many thanks Steve R If Cells.Cells(9, 3).Value = "" Then Cells.Cells(9, 8).Value = "" ElseIf Cells.Cells(9, 7).Value = "N/A" Then Cells.Cells(9, 8).Value = "0" ' Category S ElseIf Cells.Cells(9, 7) = "S" & Cells.Cells(9, 6).Value < 5 Then Cells.Cells(9, 8).Value = "0" ElseIf Cells.Cells(9, 7) = "S" & Cells.Cells(9, 6) < 10 Then Cells.Cells(9, 8).Value = "5%" ElseIf Cells.Cells(9, 7) = "S" & Cells.Cells(9, 6) 9 Then Cells.Cells(9, 8).Value = "10%" ' Category P ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) < 3 Then Cells.Cells(9, 8).Value = "0" ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) < 5 Then Cells.Cells(9, 8).Value = "5%" ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) < 10 Then Cells.Cells(9, 8).Value = "15%" ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) 10 Then Cells.Cells(9, 8).Value = "20%" Else Cells.Cells(9, 8).Value = "" End If -- Steve R |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If, Else statements
Steve,
Is this tracking as a result of this code, or does the tracking need to be on these cells? What are you trying to track, and what action do you want to take? -- HTH RP (remove nothere from the email address if mailing direct) "Steve" wrote in message ... Many thanks, I will give that a go. Can I get the VB to run only if a certain field is changed? If not, what is best Worksheet_calculate or Worksheet_change? Thanks Steve "Steve" wrote: Thanks Bob at least I don't get the error now. I need to track the changes in particular cells when running this code. at the moment I have used the: " Private Sub Worksheet_Change(ByVal Target As Range)" option but this does not change the values either when i update the cells. Any ideas? I appreciate you help. Regards Steve R "Steve" wrote: Hi Can anyone please help me? I get an "Else without If" error when running this and not sure why as it seems the same as the example in the help file. Also, what is the best way to run it if I want to run this when a certain cell is changed. Or can it only be done when the worrksheet is changed. Many thanks Steve R If Cells.Cells(9, 3).Value = "" Then Cells.Cells(9, 8).Value = "" ElseIf Cells.Cells(9, 7).Value = "N/A" Then Cells.Cells(9, 8).Value = "0" ' Category S ElseIf Cells.Cells(9, 7) = "S" & Cells.Cells(9, 6).Value < 5 Then Cells.Cells(9, 8).Value = "0" ElseIf Cells.Cells(9, 7) = "S" & Cells.Cells(9, 6) < 10 Then Cells.Cells(9, 8).Value = "5%" ElseIf Cells.Cells(9, 7) = "S" & Cells.Cells(9, 6) 9 Then Cells.Cells(9, 8).Value = "10%" ' Category P ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) < 3 Then Cells.Cells(9, 8).Value = "0" ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) < 5 Then Cells.Cells(9, 8).Value = "5%" ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) < 10 Then Cells.Cells(9, 8).Value = "15%" ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) 10 Then Cells.Cells(9, 8).Value = "20%" Else Cells.Cells(9, 8).Value = "" End If -- Steve R |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If, Else statements
Bob
Essentially, I have a spreadsheet that has various info on it but the main items I want to check is the Category, Qty and discount. Basically, if it is a certain category and the quantity it a certain value, I want to put in a level of discount. I did try without VB but it is quite complex, hence my dip into VB. Sorry to be a pain. Thanks Steve "Bob Phillips" wrote: Steve, Is this tracking as a result of this code, or does the tracking need to be on these cells? What are you trying to track, and what action do you want to take? -- HTH RP (remove nothere from the email address if mailing direct) "Steve" wrote in message ... Many thanks, I will give that a go. Can I get the VB to run only if a certain field is changed? If not, what is best Worksheet_calculate or Worksheet_change? Thanks Steve "Steve" wrote: Thanks Bob at least I don't get the error now. I need to track the changes in particular cells when running this code. at the moment I have used the: " Private Sub Worksheet_Change(ByVal Target As Range)" option but this does not change the values either when i update the cells. Any ideas? I appreciate you help. Regards Steve R "Steve" wrote: Hi Can anyone please help me? I get an "Else without If" error when running this and not sure why as it seems the same as the example in the help file. Also, what is the best way to run it if I want to run this when a certain cell is changed. Or can it only be done when the worrksheet is changed. Many thanks Steve R If Cells.Cells(9, 3).Value = "" Then Cells.Cells(9, 8).Value = "" ElseIf Cells.Cells(9, 7).Value = "N/A" Then Cells.Cells(9, 8).Value = "0" ' Category S ElseIf Cells.Cells(9, 7) = "S" & Cells.Cells(9, 6).Value < 5 Then Cells.Cells(9, 8).Value = "0" ElseIf Cells.Cells(9, 7) = "S" & Cells.Cells(9, 6) < 10 Then Cells.Cells(9, 8).Value = "5%" ElseIf Cells.Cells(9, 7) = "S" & Cells.Cells(9, 6) 9 Then Cells.Cells(9, 8).Value = "10%" ' Category P ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) < 3 Then Cells.Cells(9, 8).Value = "0" ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) < 5 Then Cells.Cells(9, 8).Value = "5%" ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) < 10 Then Cells.Cells(9, 8).Value = "15%" ElseIf Cells.Cells(9, 7) = "P" & Cells.Cells(9, 6) 10 Then Cells.Cells(9, 8).Value = "20%" Else Cells.Cells(9, 8).Value = "" End If -- Steve R |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If Statements | Excel Discussion (Misc queries) | |||
| if then statements | Excel Worksheet Functions | |||
| IF Statements (Mutliple Statements) | Excel Worksheet Functions | |||
| if and or statements | Excel Discussion (Misc queries) | |||
| IF & AND statements | Excel Worksheet Functions | |||
Hybrid Mode
