VBA ERROR>>> HELP!!!

Anonymous

I'm really annoyed of this workbook. If someone could please tell me how to fix this. I have alot of VBA on my sheet 2. They are all lined up to they work correctly but, everytime I test it, puts the beginning as a highlighted yellow.. the end as blue and gives me the error.

Beginning: Private Sub Worksheet_Change(ByVal Target As Range)

End: End Sub

I'm getting Error:

Compile error- Block If without End If

I have HAVE NO IDEA what this errors means.

If someone could please help me I would really appreciate. Thank you in advance to everyone who helps me!

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

13 answers

Anonymous

2013-02-01T20:07:00+00:00

I tried your solution and it didn't work as well.
Was this answer helpful?

0 comments No comments
Anonymous

2013-02-01T20:06:02+00:00

So I tried to do this but now I have got another error:

Compile Error: Next without if

The VBA thats giving me all these problem is:

Private Sub Worksheet_Change(ByVal Target As Range)

     If Not Intersect(Range("B10:B14"), Target) Is Nothing Then

         If Application.CountA(Range("B10:B14")) > 0 Then

             Application.EnableEvents = False

             Range("E10:E14").ClearContents

             Application.EnableEvents = True

         End If

     End If

     If Not Intersect(Range("E10:E14"), Target) Is Nothing Then

         If Application.CountA(Range("E10:E14")) > 0 Then

             Application.EnableEvents = False

             Range("B10:B14").ClearContents

             Application.EnableEvents = True

         End If

     End If

    If Not Intersect(Target, Range("Q5:Q9,Q11")) Is Nothing Then

        Application.EnableEvents = False

        Dim q As Range

        For Each q In Intersect(Target, Range("Q5:Q9,Q11"))

            If IsNumeric(q) Then

                If q > 2 Then

                    q = 2

                ElseIf q < 0 Then

                    q = 0

                End If

            End If

        Next q

    End If

If Not Intersect(Target, Range("Q10")) Is Nothing Then        On Error GoTo FallThrough        Application.EnableEvents = False        If IsNumeric(Range("Q10")) Then _            Range("Q10") = Application.Max(0, Application.Min(1, Range("Q10").Value))End If

    Application.EnableEvents = True

If Intersect(Target, Range("Q24")) Is Nothing Then

    Exit Sub

   Else

   outRow = Target.Value + 7

   With ActiveWorkbook.Worksheets("Sheet 1")

    .Cells(outRow, "O") = ActiveSheet.Range("D47")

    .Cells(outRow, "N") = ActiveSheet.Range("D46")

    .Cells(outRow, "P") = ActiveSheet.Range("D45")

    .Cells(outRow, "Q") = ActiveSheet.Range("D44")

   .Cells(outRow, "L") = ActiveSheet.Range("I15")

   .Cells(outRow, "R") = ActiveSheet.Range("D43")

   .Cells(outRow, "S") = ActiveSheet.Range("E23")

   .Cells(outRow, "T") = ActiveSheet.Range("E22")

   .Cells(outRow, "U") = ActiveSheet.Range("E21")

   .Cells(outRow, "V") = ActiveSheet.Range("B24")

   .Cells(outRow, "W") = ActiveSheet.Range("B23")

   .Cells(outRow, "X") = ActiveSheet.Range("B22")

   .Cells(outRow, "Y") = ActiveSheet.Range("F19")

   .Cells(outRow, "Z") = ActiveSheet.Range("P29")

.Cells(outRow, "AA") = ActiveSheet.Range("Q12")

End With

End If

Application.EnableEvents = True

If Intersect(Target, Range("Q24")) Is Nothing Then

    Exit Sub

   Else

   outRow = Target.Value + 378

   With ActiveWorkbook.Worksheets("Sheet 1")

    .Cells(outRow, "B") = ActiveSheet.Range("Q5")

    .Cells(outRow, "D") = ActiveSheet.Range("Q6")

    .Cells(outRow, "F") = ActiveSheet.Range("Q7")

    .Cells(outRow, "H") = ActiveSheet.Range("Q8")

   .Cells(outRow, "J") = ActiveSheet.Range("Q9")

   .Cells(outRow, "AB") = ActiveSheet.Range("Q10")

   .Cells(outRow, "AD") = ActiveSheet.Range("Q11")

   .Cells(outRow, "C") = ActiveSheet.Range("H21")

   .Cells(outRow, "E") = ActiveSheet.Range("H22")

   .Cells(outRow, "G") = ActiveSheet.Range("I22")

   .Cells(outRow, "I") = ActiveSheet.Range("J22")

   .Cells(outRow, "K") = ActiveSheet.Range("J21")

   .Cells(outRow, "AC") = ActiveSheet.Range("I14")

   .Cells(outRow, "AE") = ActiveSheet.Range("Q14")

   .Cells(outRow, "M") = ActiveSheet.Range("D6")

   .Cells(outRow, "AF") = ActiveSheet.Range("C16")

   .Cells(outRow, "AG") = ActiveSheet.Range("F5")

   .Cells(outRow, "AH") = ActiveSheet.Range("B5")

   .Cells(outRow, "AI") = ActiveSheet.Range("I21")

   .Cells(outRow, "AK") = ActiveSheet.Range("I16")

   .Cells(outRow, "AL") = ActiveSheet.Range("D26")

End With

End If

Application.EnableEvents = True

If Intersect(Target, Range("Q24")) Is Nothing Then

    Exit Sub

   Else

   outRow = Target.Value + 746

   With ActiveWorkbook.Worksheets("Sheet 1")

    .Cells(outRow, "D") = ActiveSheet.Range("B14")

    .Cells(outRow, "C") = ActiveSheet.Range("B13")

    .Cells(outRow, "E") = ActiveSheet.Range("B12")

    .Cells(outRow, "F") = ActiveSheet.Range("B11")

    .Cells(outRow, "G") = ActiveSheet.Range("B10")

   .Cells(outRow, "I") = ActiveSheet.Range("E10")

   .Cells(outRow, "H") = ActiveSheet.Range("E11")

   .Cells(outRow, "J") = ActiveSheet.Range("E12")

   .Cells(outRow, "K") = ActiveSheet.Range("E13")

   .Cells(outRow, "M") = ActiveSheet.Range("E14")

End With

End If

Dim orw As Long

    If Target.Address = "$Q$25" Then

        On Error GoTo FallThrough

        Application.EnableEvents = False

        If Application.Sum(Target) > 0 Then

            orw = Target.Value + 746

            With Sheets("Sheet 1")

                .Range("D" & orw) = Me.Range("B14").Value

                .Range("C" & orw) = Me.Range("B13").Value

                .Range("E" & orw) = Me.Range("B12").Value

                .Range("F" & orw) = Me.Range("B11").Value

                .Range("G" & orw) = Me.Range("B10").Value

                .Range("I" & orw) = Me.Range("E10").Value

                .Range("H" & orw) = Me.Range("E11").Value

                .Range("J" & orw) = Me.Range("E12").Value

                .Range("K" & orw) = Me.Range("E13").Value

                .Range("M" & orw) = Me.Range("E14").Value

            End With

        End If

    ElseIf Target.Address = "$Q$25" Then

        On Error GoTo FallThrough

        Application.EnableEvents = False

        If Application.Sum(Target) > 0 Then

            orw = Target.Value + 746

            With Sheets("Sheet 1")

                Me.Range("B5") = .Range("A" & orw).Value

                Me.Range("E24") = .Range("B" & orw).Value

                Me.Range("G500") = .Range("C" & orw).Value

            End With

        End If

    If Target.Address = "$Q$25" Then

        On Error GoTo FallThrough

        Application.EnableEvents = False

        If Application.Sum(Target) > 0 Then

            orw = Target.Value + 378

            With Sheets("Sheet 1")

                .Range("B" & orw) = Me.Range("Q5").Value

                .Range("D" & orw) = Me.Range("Q6").Value

                .Range("F" & orw) = Me.Range("Q7").Value

                .Range("H" & orw) = Me.Range("Q8").Value

                .Range("J" & orw) = Me.Range("Q9").Value

                .Range("AB" & orw) = Me.Range("Q10").Value

                .Range("AD" & orw) = Me.Range("Q11").Value

                .Range("C" & orw) = Me.Range("H21").Value

                .Range("E" & orw) = Me.Range("H22").Value

                .Range("G" & orw) = Me.Range("I22").Value

                .Range("I" & orw) = Me.Range("J22").Value

                .Range("K" & orw) = Me.Range("J21").Value

                .Range("AC" & orw) = Me.Range("I14").Value

                .Range("AE" & orw) = Me.Range("Q14").Value

                .Range("M" & orw) = Me.Range("D6").Value

                .Range("AF" & orw) = Me.Range("C16").Value

                .Range("AG" & orw) = Me.Range("F5").Value

                .Range("AH" & orw) = Me.Range("B5").Value

                .Range("AI" & orw) = Me.Range("I21").Value

                .Range("AK" & orw) = Me.Range("I16").Value

                .Range("AL" & orw) = Me.Range("D26").Value

            End With

        End If

    ElseIf Target.Address = "$Q$25" Then

        On Error GoTo FallThrough

        Application.EnableEvents = False

        If Application.Sum(Target) > 0 Then

            orw = Target.Value + 378

            With Sheets("Sheet 1")

                Me.Range("B5") = .Range("A" & orw).Value

                Me.Range("E24") = .Range("B" & orw).Value

                Me.Range("G500") = .Range("C" & orw).Value

            End With

        End If

    End If

FallThrough:

    Application.EnableEvents = True

Next

End Sub

I really don't know what to do with it. The bolded part is a VBA that I do know is having a little trouble though, it makes that the target cell has a min of 0 and max of 1... But when you press delete it goes back to 0, when it suppose to delete all content of the cell. So i'm not sure about that.

I would really appreciate the help.. THANK YOU!!!
Was this answer helpful?

0 comments No comments
Anonymous

2013-02-01T16:37:27+00:00

I may have misread your code. On further inspection, I think that your use of "End" is in your description of the code, not within the actual code itself. In this case, you have to manually ensure that every If has an End If, every With statement has a matching End With, every Do loop has a loop terminator, and so on. This is easy to find if you have indented your code properly.
Was this answer helpful?

0 comments No comments
Anonymous

2013-02-01T16:33:52+00:00

Usually, that message means that you have an IF statement that has no matching End If statement. It could also come from a Do loop without a matching Loop statement. You need to examine the code to find the If without the End If. This is vastly simpler if your code is properly indented. However, I think the problem is using the word "End" as a line label. "End" is a reserved word and should never be used (that's right, never). Use a descriptive line label thas is not a reserved word. Also, the End Sub line should be on its own line of code, not in a compound statement. E.g,.

EndOfSub:

End Sub
Was this answer helpful?

0 comments No comments
Anonymous

2013-02-01T09:49:10+00:00

Hi,

That error message can be confusing but what it means is you have opened for example

An If statemement

A with statement

A For statement

and not closed it. The reason I say it can be confusing is that the compiler doesn't always get the correct error. Take the code below where the error is a missing END IF statement. The compiler actually gives an error message of 'Next without For' which is incorrect.

Basically what you have to do is check your code for missing 'Next' statements etc. IMHO if you indent your code when writing it then the errors are easier to find.

Sub somesub()

For x = 1 To 10

If x = 3 Then

Next

End Sub
Was this answer helpful?

0 comments No comments

Share via

VBA ERROR>>> HELP!!!

13 answers