|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
If, Else statements
You are the man Bob.
Many thanks I really appreciate it "Bob Phillips" wrote: Steve, Not really sure that I have got it, but givce this a whirl and see how we stand. I'm off to bed, will check in in the morning. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim Col6, Col7 With Target If .Column = 6 Or .Column = 7 Then Application.EnableEvents = False On Error GoTo ws_exit Col6 = Me.Cells(.Row, 6).Value Col7 = Me.Cells(.Row, 7).Value Select Case Col7 Case "S": If Col6 < 5 Then Me.Cells(.Row, 8).Value = "0" ElseIf Col6 < 10 Then Me.Cells(.Row, 8).Value = "5%" Else Me.Cells(.Row, 8).Value = "10%" End If Case "P": If Col6 < 3 Then Me.Cells(.Row, 8).Value = "0" ElseIf Col6 < 5 Then Me.Cells(.Row, 8).Value = "5%" ElseIf Col6 < 10 Then Me.Cells(.Row, 8).Value = "15%" Else Me.Cells(.Row, 8).Value = "20%" End If End Select End If End With ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH RP (remove nothere from the email address if mailing direct) "Steve" wrote in message ... 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 |
| All times are GMT +1. The time now is 12:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com