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).

if A = B then

    if C = D then 'do some stuff

end if

if A = B then

    if C = D then
        'do some stuff
    end if

end if

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.

    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:aag325").Select
        ActiveSheet.Paste
End If

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.

With ActiveWorkbook.Worksheets("NewData").Sort
    .SetRange Range("g3:AAL182")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

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