End If without block If

[tays01s]

Part of a macro is below. There is an alternative block for 'PN. To get over the problem do I have to put "End if" after " ActiveSheet.Paste" then begin another "If" block? I'd prefer a method of putting both paragraphs within the 1 "IF", just for convenience/ brevity.

'EN
If Range("aaj1") = 1 Then
Range("aai3:aai182").Select
Selection.Copy
Range("k3:aag182").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveSheet.Paste

Range("g3:aal182").Select
ActiveWorkbook.Worksheets("NewData").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("NewData").Sort.SortFields.Add Key:=Range( _
"aaj3:aaj182"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("NewData").Sort
.SetRange Range("g3:AAL182")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

Range("aaj3:aaj32").Select
Selection.Copy
Range("k3:aag32").Select
ActiveSheet.Paste
End If

[Red Spot]

Next time I'd put the code in code-brackets, makes it a lot easier to read (besides that it is a forum rule).

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Above is effectively the same, if you can put what follows after the if-statement is on the same line you can skip the closing of the if-statement. If you need more room you can apply a closing statement.

[Norie]

The problem is actually a missing End With that should go after .Apply.

[Red Spot]

I noticed that but that wasnt the question

[Norie]

It is the answer to the problem though.

If the OP really wants help in structuring the code I think we would need to see more of it.

[tays01s]

I've not yet understood the answer. However, here's both alternatives of the 'If' together with their 'do stuff' component:

'EN
If Range("aaj1") = 1 Then
Range("aai3:aai182").Select
Selection.Copy
Range("k3:aag182").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveSheet.Paste

Range("g3:aal182").Select
ActiveWorkbook.Worksheets("NewData").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("NewData").Sort.SortFields.Add Key:=Range( _
"aaj3:aaj182"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("NewData").Sort
.SetRange Range("g3:AAL182")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

Range("aaj3:aaj32").Select
Selection.Copy
Range("k3:aag32").Select
ActiveSheet.Paste
End If

'PN
If Range("aaj1") = 0 Then
Range("aak3:aak182").Select
Selection.Copy
Range("k3:aag182").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveSheet.Paste

Range("g3:aal182").Select
ActiveWorkbook.Worksheets("NewData").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("NewData").Sort.SortFields.Add Key:=Range( _
"aal3:aal182"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("NewData").Sort
.SetRange Range("g3:AAL182")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

Range("aal3:aal32").Select
Selection.Copy
Range("k3:aag32").Select
ActiveSheet.Paste
End If

[Norie]

The problem is missing End Withs.

For example, you open a With here but there's no End With later in the code to close it.

The End With should be immediately after .Apply.

Please Login or Register  to view this content.

PS Please add code tags, it makes the code a lot easier to read and it's kind of a forum rule.

[tays01s]

So for the above it should have ended with...


ActiveSheet.Paste
End With
End If

[Correct?]

- Apologies for ignorance; what's a code tag?

[Norie]

No like this.

Please Login or Register  to view this content.