Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2013
    Posts
    4

    Unanswered: Block If without End If

    Hi,

    I am new to VB and need some help with my code:

    I need to run 60 different conditional statements. However I keep getting a Block If without End If error.

    If someone could shorten or modify my code so it works I would be grateful.

    This is a sample of my code (the first 6 out of 60):

    Sub AMInfo1()
    '
    ' This Macro will load the Data - Do not modify anything below expect the UserPicture information
    ' If you wish to change any AM Details then unhide spreadsheet 'AM-Info' and modify the information there; As this will automatically update this Macro code and the Directory Spreadsheet.

    '
    Range("B4").Select
    Dim COUNT As Integer
    Dim MEMBER As Integer
    Sheets("Directory").Select
    MEMBER = Range("A25").Value
    Range("H6").Select
    Rem For COUNT = 1 To 60
    Rem Next COUNT
    If MEMBER = 1 Then ActiveSheet.Pictures.Insert("E:\Directory\AM-Pics\Aled-Roberts.png").Select
    Selection.ShapeRange.IncrementLeft 588
    Selection.ShapeRange.IncrementTop 95.25
    Selection.ShapeRange.ScaleWidth 1.1924118, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 1.1924118, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleWidth 0.8902439024, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 1.0487804878, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleWidth 1.0479452055, msoFalse, msoScaleFromTopLeft
    Range("F5").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-2]C[-5])"
    Range("F7").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-4]C[-4])"
    Range("F8").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[57]C[-4])"
    Range("F9").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-6]C[-2])"
    Range("F11").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-8]C[-1])"
    Range("F13").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-10]C)"
    Range("F14").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[51]C)"
    Range("F15").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-12]C[1])"
    Range("F16").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[49]C[1])"
    Range("A1").Select
    If MEMBER = 2 Then ActiveSheet.Pictures.Insert("E:\Directory\AM-Pics\Aled-Roberts.png").Select
    Selection.ShapeRange.IncrementLeft 588
    Selection.ShapeRange.IncrementTop 95.25
    Selection.ShapeRange.ScaleWidth 1.1924118, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 1.1924118, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleWidth 0.8902439024, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 1.0487804878, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleWidth 1.0479452055, msoFalse, msoScaleFromTopLeft
    Range("F5").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-1]C[-5])"
    Range("F7").Select
    ActiveWindow.SmallScroll Down:=-6
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-3]C[-4])"
    Range("F7").Select
    With Selection
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 1
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    Range("F8").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[58]C[-4])"
    Range("F9").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-5]C[-2])"
    Range("F11").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-7]C[-1])"
    Range("F13").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-9]C)"
    Range("F15").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-11]C[1])"
    Range("F16").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[50]C[1])"
    End With
    Range("A1").Select
    If MEMBER = 3 Then
    ActiveSheet.Pictures.Insert("E:\Directory\AM-Pics\Aled-Roberts.png").Select
    Selection.ShapeRange.IncrementLeft 588
    Selection.ShapeRange.IncrementTop 95.25
    Selection.ShapeRange.ScaleWidth 1.1924118, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 1.1924118, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleWidth 0.8902439024, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 1.0487804878, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleWidth 1.0479452055, msoFalse, msoScaleFromTopLeft
    Range("F5").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!RC[-5])"
    Range("F7").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-2]C[-4])"
    Range("F8").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[59]C[-4])"
    Range("F9").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-4]C[-2])"
    Range("F11").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-6]C[-1])"
    Range("F13").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-8]C)"
    Range("F15").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-10]C[1])"
    Range("F16").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[51]C[1])"
    Range("A1").Select
    If MEMBER = 4 Then
    ActiveSheet.Pictures.Insert("E:\Directory\AM-Pics\Aled-Roberts.png").Select
    Selection.ShapeRange.IncrementLeft 588
    Selection.ShapeRange.IncrementTop 95.25
    Selection.ShapeRange.ScaleWidth 1.1924118, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 1.1924118, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleWidth 0.8902439024, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 1.0487804878, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleWidth 1.0479452055, msoFalse, msoScaleFromTopLeft
    Range("F5").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[1]C[-5])"
    Range("F7").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-1]C[-4])"
    Range("F8").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[60]C[-4])"
    Range("F9").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-3]C[-2])"
    Range("F11").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-5]C[-1])"
    Range("F13").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-7]C)"
    Range("F14").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[54]C)"
    Range("F15").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-9]C[1])"
    Range("F16").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[52]C[1])"
    Range("A1").Select
    If MEMBER = 5 Then
    ActiveSheet.Pictures.Insert("E:\Directory\AM-Pics\Aled-Roberts.png").Select
    Selection.ShapeRange.IncrementLeft 588
    Selection.ShapeRange.IncrementTop 95.25
    Selection.ShapeRange.ScaleWidth 1.1924118, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 1.1924118, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleWidth 0.8902439024, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 1.0487804878, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleWidth 1.0479452055, msoFalse, msoScaleFromTopLeft
    Range("F5").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[2]C[-5])"
    Range("F7").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!RC[-4])"
    Range("F8").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[61]C[-4])"
    Range("F9").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-2]C[-2])"
    Range("F11").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-4]C[-1])"
    Range("F11").Select
    With Selection
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 1
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    Range("F12").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[57]C[-1])"
    Range("F13").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-6]C)"
    Range("F15").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-8]C[1])"
    Range("F16").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[53]C[1])"
    End With
    Range("A1").Select
    If MEMBER = 6 Then
    ActiveSheet.Pictures.Insert("E:\Directory\AM-Pics\Aled-Roberts.png").Select
    Selection.ShapeRange.IncrementLeft 588
    Selection.ShapeRange.IncrementTop 95.25
    Selection.ShapeRange.ScaleWidth 1.1924118, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 1.1924118, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleWidth 0.8902439024, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 1.0487804878, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleWidth 1.0479452055, msoFalse, msoScaleFromTopLeft
    Range("F5").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[3]C[-5])"
    Range("F7").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[1]C[-4])"
    Range("F8").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[62]C[-4])"
    Range("F9").Select
    ActiveWindow.SmallScroll Down:=-3
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-1]C[-2])"
    Range("F11").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-3]C[-1])"
    Range("F12").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[58]C[-1])"
    Range("F13").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-5]C)"
    Range("F15").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-7]C[1])"
    Range("F16").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[54]C[1])"
    Range("A1").Select

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is that your full code?
    Code:
    If <some_condition> Then
      <do_something>
    End If
    You're missing the end ifs...
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    it helps if you indent your code (ie after starting a new conditional block push the code either 1 tab or two spaces to the right and then close the block the same width back

    eg
    Code:
    With Selection
      .HorizontalAlignment = xlLeft
      .VerticalAlignment = xlCenter
      .WrapText = True
      .Orientation = 0
      .....
    End With
    that way round finding thsi sort of error is easy (well truth to be told this sort of error rarely occurs as visually you can see where a conditonal block starts and finishes
    conditiomnal blocks include
    IF conditiuon THEN
    ELSE
    ENDIF

    WHERE condition
    ...
    WEND

    WITH object

    WEND

    FOR loops

    SELECT CASE variable
    ...and so on.

    Another thing to avoid is the vb short hand
    Code:
    IF condition THEN statement
    and insterd always use
    Code:
    IF condition THEN
      statement
    endif
    why? well it doesn't sve anything performance wise
    it stops you gettign your knickers in a twist.
    ...it sprobably



    FWIW I think you would stylistically be better off using a select case in you code if there are a series of mutually exclusive options
    eg
    Select case Member
    Case = 1
    Case =2
    default:
    end select

    Im also guessing the original author of this code had good reason to sya:-
    If you wish to change any AM Details then unhide spreadsheet 'AM-Info' and modify the information there; As this will automatically update this Macro code and the Directory Spreadsheet.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2013
    Posts
    4
    This isn't the full code as there are 60 statements; However I have chosen the first 6 as this shows you the information in the code.

    Would it be possible for someone to re-write the code to either show how the 'select case' would work in the code or the 'End If' (as I am new to VB). - As I would then use this examble to update the complete code to see if it works.

    The information AM Details, etc - was there as an instructions just to ensure anyone who was reading the code would know the '=T formula' was being used.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    'insert common code
    select case member
    case = 1 'insert code specific to member = 1
    case = 2 'insert code specific to member = 2
    case = ... 'insert code specific to member = ...
    default 'insert code for other othe rmeber values not covered above
    end select
    'insert common code
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Apr 2013
    Posts
    4
    Thank you for your help:

    I modified the code (as shown below) but I am getting a new error - End Select without Select Case.

    If someone could advise / modify the code to make it work I would be grateful...again.

    -----------------

    Dim COUNT As Integer
    Dim MEMBER As Integer
    Sheets("Directory").Select
    MEMBER = Range("A25").Value
    Range("H6").Select
    Rem For COUNT = 1 To 60
    Rem Next COUNT
    Select Case MEMBER
    Case Is = 1
    Range("F5").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-2]C[-5])"
    Range("F7").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-4]C[-4])"
    Range("F8").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[57]C[-4])"
    Range("F9").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-6]C[-2])"
    Range("F11").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-8]C[-1])"
    Range("F13").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-10]C)"
    Range("F14").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[51]C)"
    Range("F15").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[-12]C[1])"
    Range("F16").Select
    ActiveCell.FormulaR1C1 = "=T('AM-Info'!R[49]C[1])"
    ActiveSheet.Shapes.AddShape(msoShapeRectangle, 588.75, 95.25, 109.5, 127.5).Select
    ActiveSheet.Shapes.Range(Array("Rectangle 2")).Select
    With Selection.ShapeRange.Fill
    .Visible = msoTrue
    .UserPicture_ "E:/Directory/AM-Pics/Aled-Roberts.PNG"
    .TextureTile = msoFalse
    End Select
    Range("A1").Select
    End Sub

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its one of the quirks of VBA that soemtimes it reports an error correctly but gives an unhelpful error message

    in this case its assumed the 'end select' is an end with, and is still looking for the end select.

    this sort of error is easier to spot if you indent your code as suggested in post #3
    Code:
        With Selection.ShapeRange.Fill
          .Visible = msoTrue
          .UserPicture_ "E:/Directory/AM-Pics/Aled-Roberts.PNG"
          .TextureTile = msoFalse
        End with
      End Select
    it also helps when posting code here usign the [ c o d e ] & [ / c o d e ] (without the spaces so start code blocks with [code] and end such blocks with [ / c o d e ]
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •