Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Block If without End If

0

Hi Guys,

i am getting error Block If without End If & i tried to do the same but i was unable to do it so plz help me in the same 

Thnx For the Help

Sub step6()

Dim wb1 As Workbook
Dim wb3 As Workbook
Dim wsh1 As Worksheet
Dim wsh3 As Worksheet

Set wb1 = Workbooks("Nest.xlsm")
Set wb3 = Workbooks("BasketOrder.csv")

Set wsh1 = wb1.Sheets(1)
Set wsh3 = wb3.Sheets(1)

Application.ScreenUpdating = False
Application.DisplayAlerts = False

wb3.Activate

If wsh3.Range("A2").Value <> "" Or wsh3.Range("A2").Value = "" Then
    If VBA.Asc(UCase(wsh3.Range("A2"))) < 64 And VBA.Asc(UCase(wsh3.Range("A2"))) > 91 Then
Exit Sub

Else
    wb1.Activate
    ''input box
    at6 = InputBox("Enter a Number to invest", "Enter a Number to invest")

    Do While Not (IsNumeric(at6))
    at6 = InputBox("Enter a Number to invest", "Enter a Number to invest")
    Loop


    au6 = wsh1.Range("Ax5").Value
    If au6 = "" Then
        wsh1.Range("AX5").Value = at6 * 0.25
        av6 = at6 * 0.75
        Else
        av6 = at6 - au6
    End If


    aw6 = wsh1.Range("Z5").Value
    If aw6 = "" Then
     ax6 = av6 * 3
        Else
        ax6 = av6 * aw6
    End If

    ''count1
    wb3.Activate
    ay6 = wsh3.Range("A2").End(xlDown).Row - 1
    az6 = ay6 / 2

    ''count2
    wb1.Activate
    ba6 = wsh1.Range("A2").End(xlDown).Row
    bb6 = 0
    For bc6 = 2 To ba6
        If wsh1.Range("Q" & bc6).Value >= 5 And (wsh1.Range("H" & bc6).Value < wsh1.Range("I" & bc6).Value) And wsh1.Range("T" & bc6).Value = "" Then
            bb6 = bb6 + 1
        End If
    Next bc6

    ''count3
    wb1.Activate
    bd6 = wsh1.Range("A2").End(xlDown).Row
    be6 = 0
    For bc6 = 2 To bd6
        If wsh1.Range("Q" & bc6).Value <= -5 And (wsh1.Range("H" & bc6).Value > wsh1.Range("J" & bc6).Value) And wsh1.Range("T" & bc6).Value = "" Then
            be6 = be6 + 1
        End If
    Next bc6

    bg6 = az6 + bb6 + be6
    bh6 = ax6

    bi6 = bh6 / bg6

    wb3.Activate
    bj6 = wsh3.Range("A2").End(xlDown).Row

    For bc6 = 2 To bj6 Step 2
        wsh3.Range("H" & bc6).Value = Round(wsh3.Range("J" & bc6).Value / bi6, 0)
        wsh3.Range("H" & bc6 + 1).Value = Round(wsh3.Range("J" & bc6).Value / bi6, 0)
    Next bc6

    For bc6 = 2 To bj6 Step 2
       If (VBA.Asc(wsh3.Range("H" & bc6)) = 45 Or VBA.Asc(wsh3.Range("H" & bc6)) = 48) And VBA.Asc(wsh3.Range("H" & bc6)) < 45 And VBA.Asc(wsh3.Range("H" & bc6)) > 58 Then
            bm6 = wsh3.Range("C" & bc6).Value
            wb1.Activate
                wsh1.Columns("G:G").Select
                Selection.Find(What:=bm6 & "-EQ", After:=ActiveCell, LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False).Activate

                ActiveCell.Offset(0, 12).ClearContents
                ActiveCell.Offset(0, 13).ClearContents
            wb3.Activate

            wsh3.Rows(bc6 & ":" & bc6 + 1).EntireRow.Delete
        End If
    Next bc6

    ''removing rows
    For bc6 = 2 To bj6
       If wsh3.Range("H" & bc6).Value <> "" Or wsh3.Range("H" & bc6).Value = "" Then
          If VBA.Asc(wsh3.Range("H" & bc6)) < 47 And VBA.Asc(wsh3.Range("H" & bc6)) > 58 Then
             Exit For
           Else
               If (VBA.Asc(wsh3.Range("H" & bc6)) = 45 Or VBA.Asc(wsh3.Range("H" & bc6)) = 48) And VBA.Asc(wsh3.Range("H" & bc6)) < 45 And VBA.Asc(wsh3.Range("H" & bc6)) > 58 Then
                wsh3.Rows(bc6 & ":" & bc6).EntireRow.Delete
                bc6 = bc6 - 1
            End If
        End If
    End If
    Next bc6
''first if
End If

End If

wb3.Save

Dim bf6 As Long
Dim bk6 As Long
Dim bl6 As Long
Dim bs6 As Long
Dim bn6 As String
Dim bo6 As String
Dim bp6 As String
Dim bq6 As String
Dim br6 As String


bo6 = "C"
bp6 = "K"
bq6 = "H"
br6 = "L"

If wsh3.Range("A2").Value <> "" Or wsh3.Range("A2").Value = "" Then
    If VBA.Asc(UCase(wsh3.Range("A2"))) < 64 And VBA.Asc(UCase(wsh3.Range("A2"))) > 91 Then
Exit Sub

Else
bs6 = Int(7 + Rnd * (9999999 - 2 + 1))


bn6 = "C:\Users\WolfieeeStyle\Desktop\" & bs6 & ".txt"


bf6 = FreeFile6


Open bn6 For Output As bf6

bk6 = wsh3.Range("A" & Rows.Count).End(xlUp).Row

For bl6 = 1 To bk6
Print #bf6, Cells(bl6, bo6),
Print #bf6, Cells(bl6, bp6),
Print #bf6, Cells(bl6, bq6),
Print #bf6, Cells(bl6, br6)
Next bl6


Close #bf6
End If

If wsh3.Range("A2").Value <> "" Or wsh3.Range("A2").Value = "" Then
    If VBA.Asc(UCase(wsh3.Range("A2"))) < 64 And VBA.Asc(UCase(wsh3.Range("A2"))) > 91 Then
Exit Sub

Else
pass = InputBox("Plz Upload Basket order File and Enter Password : password ", "Upload Basket order File and Enter Password : password ")

Do While pass <> "Uploaded"
pass = InputBox("Plz Upload Basket order File and Enter Password : password ", "Upload Basket order File and Enter Password : password ")
Loop

End If

Application.DisplayAlerts = True
Application.ScreenUpdating = True

Application.Wait Now + 0.000001
Call step1

End Sub
Answer
Discuss

Discussion

Rider

My revised Answer just corrected a couple of typos.
John_Ru (rep: 3992) Sep 20, '22 at 4:27 pm
Add to Discussion

Answers

0

Rider

(In future, please attach a representative Excel file- it is very difficult to understand what's happening without one and we don't have the time to construct a file with cells and appropriate which might work).

Without such a file, I tried to solve your problem by indenting the code properly (in my opinion, so for every If xxx, With xxx , Doxxx  etc. (and outdent for each End xxx or Loop.

The result is shown below and indicated that you were missing two End Ifs (added in bold at the end. These might not be in the correct place but with them VBA does not give the error.

Sub step6()

Dim wb1 As Workbook
Dim wb3 As Workbook
Dim wsh1 As Worksheet
Dim wsh3 As Worksheet

Set wb1 = Workbooks("Nest.xlsm")
Set wb3 = Workbooks("BasketOrder.csv")

Set wsh1 = wb1.Sheets(1)
Set wsh3 = wb3.Sheets(1)

'Application.ScreenUpdating = False
'Application.DisplayAlerts = False

wb3.Activate

If wsh3.Range("A2").Value <> "" Or wsh3.Range("A2").Value = "" Then
    If VBA.Asc(UCase(wsh3.Range("A2"))) < 64 And VBA.Asc(UCase(wsh3.Range("A2"))) > 91 Then
        Exit Sub


        Else
        wb1.Activate
        ''input box
        at6 = InputBox("Enter a Number to invest", "Enter a Number to invest")

        Do While Not (IsNumeric(at6))
            at6 = InputBox("Enter a Number to invest", "Enter a Number to invest")
        Loop


        au6 = wsh1.Range("Ax5").Value
        If au6 = "" Then
            wsh1.Range("AX5").Value = at6 * 0.25
            av6 = at6 * 0.75
            Else
            av6 = at6 - au6
        End If


        aw6 = wsh1.Range("Z5").Value
        If aw6 = "" Then
            ax6 = av6 * 3
            Else
            ax6 = av6 * aw6
        End If

        ''count1
        wb3.Activate
        ay6 = wsh3.Range("A2").End(xlDown).Row - 1
        az6 = ay6 / 2

        ''count2
        wb1.Activate
        ba6 = wsh1.Range("A2").End(xlDown).Row
        bb6 = 0
        For bc6 = 2 To ba6
            If wsh1.Range("Q" & bc6).Value >= 5 And (wsh1.Range("H" & bc6).Value < wsh1.Range("I" & bc6).Value) And wsh1.Range("T" & bc6).Value = "" Then
                bb6 = bb6 + 1
            End If
        Next bc6

        ''count3
        wb1.Activate
        bd6 = wsh1.Range("A2").End(xlDown).Row
        be6 = 0
        For bc6 = 2 To bd6
            If wsh1.Range("Q" & bc6).Value <= -5 And (wsh1.Range("H" & bc6).Value > wsh1.Range("J" & bc6).Value) And wsh1.Range("T" & bc6).Value = "" Then
                be6 = be6 + 1
            End If
        Next bc6

        bg6 = az6 + bb6 + be6
        bh6 = ax6

        bi6 = bh6 / bg6

        wb3.Activate
        bj6 = wsh3.Range("A2").End(xlDown).Row

        For bc6 = 2 To bj6 Step 2
            wsh3.Range("H" & bc6).Value = Round(wsh3.Range("J" & bc6).Value / bi6, 0)
            wsh3.Range("H" & bc6 + 1).Value = Round(wsh3.Range("J" & bc6).Value / bi6, 0)
        Next bc6

        For bc6 = 2 To bj6 Step 2
            If (VBA.Asc(wsh3.Range("H" & bc6)) = 45 Or VBA.Asc(wsh3.Range("H" & bc6)) = 48) And VBA.Asc(wsh3.Range("H" & bc6)) < 45 And VBA.Asc(wsh3.Range("H" & bc6)) > 58 Then
                bm6 = wsh3.Range("C" & bc6).Value
                wb1.Activate
                 wsh1.Columns("G:G").Select
                 Selection.Find(What:=bm6 & "-EQ", After:=ActiveCell, LookIn:=xlFormulas, _
                 LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                 MatchCase:=False, SearchFormat:=False).Activate

                 ActiveCell.Offset(0, 12).ClearContents
                 ActiveCell.Offset(0, 13).ClearContents
                wb3.Activate

                wsh3.Rows(bc6 & ":" & bc6 + 1).EntireRow.Delete
            End If
        Next bc6

        ''removing rows
        For bc6 = 2 To bj6
            If wsh3.Range("H" & bc6).Value <> "" Or wsh3.Range("H" & bc6).Value = "" Then
                If VBA.Asc(wsh3.Range("H" & bc6)) < 47 And VBA.Asc(wsh3.Range("H" & bc6)) > 58 Then
                    Exit For
                    Else
                    If (VBA.Asc(wsh3.Range("H" & bc6)) = 45 Or VBA.Asc(wsh3.Range("H" & bc6)) = 48) And VBA.Asc(wsh3.Range("H" & bc6)) < 45 And VBA.Asc(wsh3.Range("H" & bc6)) > 58 Then
                        wsh3.Rows(bc6 & ":" & bc6).EntireRow.Delete
                        bc6 = bc6 - 1
                    End If
                End If
            End If
        Next bc6
        ''first if
    End If

End If

wb3.Save

Dim bf6 As Long
Dim bk6 As Long
Dim bl6 As Long
Dim bs6 As Long
Dim bn6 As String
Dim bo6 As String
Dim bp6 As String
Dim bq6 As String
Dim br6 As String


bo6 = "C"
bp6 = "K"
bq6 = "H"
br6 = "L"

If wsh3.Range("A2").Value <> "" Or wsh3.Range("A2").Value = "" Then
    If VBA.Asc(UCase(wsh3.Range("A2"))) < 64 And VBA.Asc(UCase(wsh3.Range("A2"))) > 91 Then
        Exit Sub

        Else
        bs6 = Int(7 + Rnd * (9999999 - 2 + 1))


        bn6 = "C:\Users\WolfieeeStyle\Desktop\" & bs6 & ".txt"


        bf6 = FreeFile6


        Open bn6 For Output As bf6

        bk6 = wsh3.Range("A" & Rows.Count).End(xlUp).Row

        For bl6 = 1 To bk6
            Print #bf6, Cells(bl6, bo6),
            Print #bf6, Cells(bl6, bp6),
            Print #bf6, Cells(bl6, bq6),
            Print #bf6, Cells(bl6, br6)
        Next bl6


        Close #bf6
    End If

    If wsh3.Range("A2").Value <> "" Or wsh3.Range("A2").Value = "" Then
        If VBA.Asc(UCase(wsh3.Range("A2"))) < 64 And VBA.Asc(UCase(wsh3.Range("A2"))) > 91 Then
            Exit Sub

            Else
            pass = InputBox("Plz Upload Basket order File and Enter Password : password ", "Upload Basket order File and Enter Password : password ")

            Do While pass <> "Uploaded"
                pass = InputBox("Plz Upload Basket order File and Enter Password : password ", "Upload Basket order File and Enter Password : password ")
            Loop

        End If

        Application.DisplayAlerts = True
        Application.ScreenUpdating = True

        Application.Wait Now + 0.000001

    End If ' missing End If #1
End If  ' missing End If #2

Call step1

End Sub

I don't know if this works on your file but hope this fixes things for you (or points you in the right direction at least).

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login