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

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 wsh1 = wb1.Sheets(1)
Set wsh3 = wb3.Sheets(1)

Application.ScreenUpdating = 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

Loop

End If

Application.ScreenUpdating = True

Application.Wait Now + 0.000001
Call step1

End Sub``````

Discussion

Rider

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

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 wsh1 = wb1.Sheets(1)
Set wsh3 = wb3.Sheets(1)

'Application.ScreenUpdating = 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

Loop

End If

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

Discussion

Didn't that help?
John_Ru (rep: 5722) Oct 4, '22 at 7:04 am