Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compile Error: Block If without End if
This is my first time using a Microsoft Excel Macro and I'm trying to
run the following code and keep getting the: Compile Error: Block If without End if message and I can't figure out why Sub P() ' ' Macro1 Macro ' Changing FedEx Descriptions ' ' Keyboard Shortcut: Ctrl+Shift+X ' If Range("K2:K999").Select = "02" Then Range("M2:M999").Select = "FedEx Ground" Else If Range("K2:K999").Select = "03" Then Range("M2:M999").Select = "FedEx 2Day" Else If Range("K2:K999").Select = "" Then Range("M2:M999").Select = "" End If End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compile Error: Block If without End if
Whenever you have an "If" line that ends in "Then" you need an End If.
The exception to this rule is, when there is only 1 instruction to perform when the If is "true" you can group the If and the instruction on a single line. So your code might look like this: Sub P() If Range("K2:K999").Select = "02" Then Range("M2:M999").Select = "FedEx Ground" If Range("K2:K999").Select = "03" Then Range("M2:M999").Select = "FedEx 2Day" If Range("K2:K999").Select = "" Then Range("M2:M999").Select = "" End Sub Or, you could use Else If, like this: Sub P() If Range("K2:K999").Select = "02" Then Range("M2:M999").Select = "FedEx Ground" ElseIf Range("K2:K999").Select = "03" Then Range("M2:M999").Select = "FedEx 2Day" ElseIf Range("K2:K999").Select = "" Then Range("M2:M999").Select = "" End If End Sub Or, just because there are numerous ways to skin this cat, you could use a CASE structure. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compile Error: Block If without End if
I see 3 IF statements, but only one END IF.
wrote in message oups.com... This is my first time using a Microsoft Excel Macro and I'm trying to run the following code and keep getting the: Compile Error: Block If without End if message and I can't figure out why Sub P() ' ' Macro1 Macro ' Changing FedEx Descriptions ' ' Keyboard Shortcut: Ctrl+Shift+X ' If Range("K2:K999").Select = "02" Then Range("M2:M999").Select = "FedEx Ground" Else If Range("K2:K999").Select = "03" Then Range("M2:M999").Select = "FedEx 2Day" Else If Range("K2:K999").Select = "" Then Range("M2:M999").Select = "" End If End Sub |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compile Error: Block If without End if
It looks like you want to check each of those values in column K.
Option Explicit Sub P2() dim LastRow as long dim iRow as long with activesheet lastrow = .cells(.rows.count,"K").end(xlup).row for irow = 2 to lastrow if .range("K" & irow).value = "02" then .range("M" & irow).value = "FedEx Ground" elseif .range("K" & irow).value = "03" then .range("M" & irow).value = "FedEx 2Day" elseif .range("K" & irow).value = "" then .range("M" & irow).value = "" end if next irow end with End Sub This actually looks for the Text "02" in the cell--not the number formatted to have leading 0's. That could be very important. You may want to change: if .range("K" & irow).value = "02" then to if .range("K" & irow).value = 2 then (and same with "03" and 3) wrote: This is my first time using a Microsoft Excel Macro and I'm trying to run the following code and keep getting the: Compile Error: Block If without End if message and I can't figure out why Sub P() ' ' Macro1 Macro ' Changing FedEx Descriptions ' ' Keyboard Shortcut: Ctrl+Shift+X ' If Range("K2:K999").Select = "02" Then Range("M2:M999").Select = "FedEx Ground" Else If Range("K2:K999").Select = "03" Then Range("M2:M999").Select = "FedEx 2Day" Else If Range("K2:K999").Select = "" Then Range("M2:M999").Select = "" End If End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel compile error. Only some computers? | Excel Discussion (Misc queries) | |||
compile error in hidden module: autoexecnew - how do I get rid? | Charts and Charting in Excel | |||
Compile error in hidden module | Excel Discussion (Misc queries) | |||
Compile Errors with Different versions of Excel | Excel Discussion (Misc queries) |