End if Without Block If Error

R

Revale

New Member
Joined
Aug 8, 2015
Messages
3
Hi All, As im sure youve all heard a million times I new to VBA and I am self teaching myself VBA for work and self betterment.

I set myself a personal project for home rather than always stuff for work to keep a stock of what we have in our multiple freezers. The problem i have is exactly what the subject line is and im sure you guys can spot it straight away. i have adapted some course material and tried to fix myself with google searches etc but you guys are my last resort. forgive me if i do not post in the Correct format as i am a new subscriber even though i've worshipped Bill Jelen for a long time!
The Bolded END IF statement at the end is where the debugger is highlighting My suspicions are multiple Else If statements? The loops somehow, Enlightenment please :) Thank you in advance for any advice.
Here is the Code:

Rich (BB code): 
Option Explicit

Sub FreezerUpdate()

Dim Freezer As String
Dim ItemId As Long
Dim BrandName As String
Dim ProductName As String
Dim Qty As String ' Delivery
Dim Percent As String 
Dim KeepSearching As Boolean
Dim RowNum As Long
Dim NewItemID As Long
Dim i As Integer
Dim Id As Integer
Dim NumNewStock As Integer

Range("A2").Select
    ActiveCell.FormulaR1C1 = ""
    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[1]&"" ""&RC[2],'Freezer Contents'!C:C[6],2,FALSE),"""")"
    Range("A2").Select
    Selection.AutoFill Destination:=Range("Table1[ItemID]")
    Range("Table1[ItemID]").Select

i = 2
Id = 2
Do Until Cells(i, 2).Value = ""

If Cells(Id, 1).Value = "" Then

' the below section was a recorded macro to sort a column in numeric  'smallest to largest order in order to determine last ItemID
Sheets("Freezer Contents").Select
        ActiveWorkbook.Worksheets("Freezer Contents").ListObjects("FreezerContents"). _
        Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Freezer Contents").ListObjects("FreezerContents"). _
        Sort.SortFields.Add Key:=Range("FreezerContents[[#All],[ItemId]]"), SortOn _
        :=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Freezer Contents").ListObjects( _
        "FreezerContents").Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Range("B2").Select
         Selection.End(xlDown).Select
        NewItemID = ActiveCell.Value + 1
       Sheets("Bought").Select
        Cells(Id, 1) = NewItemID
        Id = Id + 1
    End If

NumNewStock = Application.CountA(Range("A:A")) ' Determines total number of rows to update into Freezer Sheet
             
Worksheets("Bought").Activate

Do Until Cells(i, 1).Value = ""

ItemId = Cells(i, 1).Value
BrandName = Cells(i, 2).Value
ProductName = Cells(i, 3).Value
Qty = Cells(i, 4).Value
Percent = Cells(i, 5).Value
Freezer = Cells(i, 6).Value
KeepSearching = True
RowNum = 3

Worksheets("Freezer Contents").Activate

Do Until KeepSearching = False

    If Cells(RowNum, 2).Value = ItemId Then
        If Cells(RowNum, 5).Value <> ("NA") Then
        Cells(RowNum, 5).Value = Cells(RowNum, 5).Value + Qty
        Cells(RowNum, 6).Value = Cells(RowNum, 6).Value + Percent
    
    ElseIf Cells(RowNum, 2).Value = ItemId Then
    If Cells(RowNum, 6).Value = ("NA") Then
    KeepSearching = False
    
    ElseIf Cells(RowNum, 1).Value = "" Then
        Do Until KeepSearching = False
        Cells(RowNum, 2).Value = NewItemID
        Cells(RowNum, 3).Value = BrandName
        Cells(RowNum, 4).Value = ProductName
        Cells(RowNum, 5).Value = Qty
        Cells(RowNum, 6).Value = Percent
        Cells(RowNum, 7).Value = Freezer
        KeepSearching = False
    End
    RowNum = RowNum + 1
    End If
Loop

i = i + 1
Worksheets("Bought").Activate

Loop ' Exterior Loop for the Bought sheet
Loop

Range("A2").Select
Range("A2:F" & NumNewStock).ClearContents

MsgBox "The Freezer Contents has been updated"

End Sub
 
Last edited by a moderator:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
You need to add an End If for every If you have. So, you need several more.
 
Upvote 0
I tried what you suggested as below, still receiving the same error. thanks for the quick response though. I was worried about your intitial suggestion as my if statements are within a loop. But I tried to no avail anyways. Is it ok to have Multiple Else If statements within a loop, as thats one of the things i have added to the original code.

Thanks again.

Code: 
Option Explicit


Sub FreezerUpdate()


Dim Freezer As String
Dim ItemId As Long
Dim BrandName As String
Dim ProductName As String
Dim Qty As String ' Delivery
Dim Percent As String ' New
Dim KeepSearching As Boolean
Dim RowNum As Long
Dim NewItemID As Long
Dim i As Integer
Dim Id As Integer
Dim NumNewStock As Integer


Range("A2").Select
    ActiveCell.FormulaR1C1 = ""
    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[1]&"" ""&RC[2],'Freezer Contents'!C:C[6],2,FALSE),"""")"
    Range("A2").Select
    Selection.AutoFill Destination:=Range("Table1[ItemID]")
    Range("Table1[ItemID]").Select


i = 2
Id = 2
Do Until Cells(i, 2).Value = ""


If Cells(Id, 1).Value = "" Then




' the below section was a recorded macro to sort a column in numeric smallest to largest order in
' order to determine last ItemID
Sheets("Freezer Contents").Select
        ActiveWorkbook.Worksheets("Freezer Contents").ListObjects("FreezerContents"). _
        Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Freezer Contents").ListObjects("FreezerContents"). _
        Sort.SortFields.Add Key:=Range("FreezerContents[[#All],[ItemId]]"), SortOn _
        :=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Freezer Contents").ListObjects( _
        "FreezerContents").Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Range("B2").Select
         Selection.End(xlDown).Select
        NewItemID = ActiveCell.Value + 1
       Sheets("Bought").Select
        Cells(Id, 1) = NewItemID
        Id = Id + 1
    End If


NumNewStock = Application.CountA(Range("A:A")) ' Determines total number of rows to update into Freezer Sheet
             
Worksheets("Bought").Activate


Do Until Cells(i, 1).Value = ""


ItemId = Cells(i, 1).Value
BrandName = Cells(i, 2).Value
ProductName = Cells(i, 3).Value
Qty = Cells(i, 4).Value
Percent = Cells(i, 5).Value
Freezer = Cells(i, 6).Value
KeepSearching = True
RowNum = 3


Worksheets("Freezer Contents").Activate


Do Until KeepSearching = False


    If Cells(RowNum, 2).Value = ItemId Then
        If Cells(RowNum, 5).Value <> ("NA") Then
        Cells(RowNum, 5).Value = Cells(RowNum, 5).Value + Qty
        Cells(RowNum, 6).Value = Cells(RowNum, 6).Value + Percent
    End If
    
    ElseIf Cells(RowNum, 2).Value = ItemId Then
    If Cells(RowNum, 6).Value = ("NA") Then
    KeepSearching = False
    End If
    
    ElseIf Cells(RowNum, 1).Value = "" Then
        Do Until KeepSearching = False
        Cells(RowNum, 2).Value = NewItemID
        Cells(RowNum, 3).Value = BrandName
        Cells(RowNum, 4).Value = ProductName
        Cells(RowNum, 5).Value = Qty
        Cells(RowNum, 6).Value = Percent
        Cells(RowNum, 7).Value = Freezer
        KeepSearching = False
    End If
    RowNum = RowNum + 1
    End If
Loop




i = i + 1
Worksheets("Bought").Activate




Loop ' Exterior Loop for the Bought sheet
Loop




Range("A2").Select
Range("A2:F" & NumNewStock).ClearContents


MsgBox "The Freezer Contents has been updated"


End Sub
 
Last edited:
Upvote 0
If you indent your code, every For should have a Next directly below, every Do a Loop. See in red.

Code: 
Sub FreezerUpdate()
  Dim Freezer       As String
  Dim ItemId        As Long
  Dim BrandName     As String
  Dim ProductName   As String
  Dim Qty           As String  ' Delivery
  Dim Percent       As String  ' New
  Dim KeepSearching As Boolean
  Dim RowNum        As Long
  Dim NewItemID     As Long
  Dim i             As Integer
  Dim Id            As Integer
  Dim NumNewStock   As Integer

  Range("A2").Select
  ActiveCell.FormulaR1C1 = ""
  Range("A2").Select
  ActiveCell.FormulaR1C1 = _
  "=IFERROR(VLOOKUP(RC[1]&"" ""&RC[2],'Freezer Contents'!C:C[6],2,FALSE),"""")"
  Range("A2").Select
  Selection.AutoFill Destination:=Range("Table1[ItemID]")
  Range("Table1[ItemID]").Select

  i = 2
  Id = 2
  Do Until Cells(i, 2).Value = ""
    If Cells(Id, 1).Value = "" Then
      ' the below section was a recorded macro to sort a column in numeric smallest to largest order in
      ' order to determine last ItemID
      Sheets("Freezer Contents").Select
      ActiveWorkbook.Worksheets("Freezer Contents").ListObjects("FreezerContents"). _
          Sort.SortFields.Clear
      ActiveWorkbook.Worksheets("Freezer Contents").ListObjects("FreezerContents"). _
          Sort.SortFields.Add Key:=Range("FreezerContents[[#All],[ItemId]]"), SortOn _
                                                                              :=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      With ActiveWorkbook.Worksheets("Freezer Contents").ListObjects( _
           "FreezerContents").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
      End With
      Range("B2").Select
      Selection.End(xlDown).Select
      NewItemID = ActiveCell.Value + 1
      Sheets("Bought").Select
      Cells(Id, 1) = NewItemID
      Id = Id + 1
    End If

    NumNewStock = Application.CountA(Range("A:A"))  ' Determines total number of rows to update into Freezer Sheet
    Worksheets("Bought").Activate

    Do Until Cells(i, 1).Value = ""
      ItemId = Cells(i, 1).Value
      BrandName = Cells(i, 2).Value
      ProductName = Cells(i, 3).Value
      Qty = Cells(i, 4).Value
      Percent = Cells(i, 5).Value
      Freezer = Cells(i, 6).Value
      KeepSearching = True
      RowNum = 3
      
      Worksheets("Freezer Contents").Activate
      Do Until KeepSearching = False
        If Cells(RowNum, 2).Value = ItemId Then
          If Cells(RowNum, 5).Value <> ("NA") Then
            Cells(RowNum, 5).Value = Cells(RowNum, 5).Value + Qty
            Cells(RowNum, 6).Value = Cells(RowNum, 6).Value + Percent
          End If

        ElseIf Cells(RowNum, 2).Value = ItemId Then
          If Cells(RowNum, 6).Value = ("NA") Then
            KeepSearching = False
          End If

        ElseIf Cells(RowNum, 1).Value = "" Then
[COLOR="#FF0000"]          Do Until KeepSearching = False[/COLOR]            Cells(RowNum, 2).Value = NewItemID
            Cells(RowNum, 3).Value = BrandName
            Cells(RowNum, 4).Value = ProductName
            Cells(RowNum, 5).Value = Qty
            Cells(RowNum, 6).Value = Percent
            Cells(RowNum, 7).Value = Freezer
            KeepSearching = False
[COLOR="#FF0000"]          End If[/COLOR]
          RowNum = RowNum + 1
        End If
      Loop

      i = i + 1
      Worksheets("Bought").Activate

    Loop  ' Exterior Loop for the Bought sheet
  Loop

  Range("A2").Select
  Range("A2:F" & NumNewStock).ClearContents

  MsgBox "The Freezer Contents has been updated"
End Sub
 
Upvote 0
Your end ifs need to go and the end of the whole If cycle. Look below and note how I insert at each If so that I can see where I need an end if

Code: 
Do Until KeepSearching = False




    If Cells(RowNum, 2).Value = ItemId Then
        If Cells(RowNum, 5).Value <> ("NA") Then
        Cells(RowNum, 5).Value = Cells(RowNum, 5).Value + Qty
        Cells(RowNum, 6).Value = Cells(RowNum, 6).Value + Percent
            ElseIf Cells(RowNum, 2).Value = ItemId Then
                If Cells(RowNum, 6).Value = ("NA") Then
                KeepSearching = False
                    ElseIf Cells(RowNum, 1).Value = "" Then
                    Do Until KeepSearching = False
                    Cells(RowNum, 2).Value = NewItemID
                    Cells(RowNum, 3).Value = BrandName
                    Cells(RowNum, 4).Value = ProductName
                    Cells(RowNum, 5).Value = Qty
                    Cells(RowNum, 6).Value = Percent
                    Cells(RowNum, 7).Value = Freezer
                    KeepSearching = False
    RowNum = RowNum + 1
                     
                End If
            End If
         
    End If
Loop
 
Last edited:
Upvote 0
Try amending your code from the do loop to the code below (untested)


Code: 
Do Until Cells(i, 1).Value = ""


            ItemId = Cells(i, 1).Value
            BrandName = Cells(i, 2).Value
            ProductName = Cells(i, 3).Value
            Qty = Cells(i, 4).Value
            Percent = Cells(i, 5).Value
            Freezer = Cells(i, 6).Value
            KeepSearching = True
            RowNum = 3
            'Worksheets("Freezer Contents").Activate
            Do Until KeepSearching = False
                If Cells(RowNum, 2).Value = ItemId Then
                    If Cells(RowNum, 5).Value <> ("NA") Then
                        Cells(RowNum, 5).Value = Cells(RowNum, 5).Value + Qty
                        Cells(RowNum, 6).Value = Cells(RowNum, 6).Value + Percent
                    End If

                ElseIf Cells(RowNum, 2).Value = ItemId Then
                    If Cells(RowNum, 6).Value = ("NA") Then
                        KeepSearching = False
                    End If

                ElseIf Cells(RowNum, 1).Value = "" Then
                    Do Until KeepSearching = False
                        Cells(RowNum, 2).Value = NewItemID
                        Cells(RowNum, 3).Value = BrandName
                        Cells(RowNum, 4).Value = ProductName
                        Cells(RowNum, 5).Value = Qty
                        Cells(RowNum, 6).Value = Percent
                        Cells(RowNum, 7).Value = Freezer
                        KeepSearching = False
                    
                    RowNum = RowNum + 1
                    Loop
                End If
            
            i = i + 1
            Worksheets("Bought").Activate
        Loop    ' Exterior Loop for the Bought sheet
    Loop

Edit: seeing as a couple of others have posted code while I was typing and probably tested their code make sure you try theirs first.
 
Last edited:
Upvote 0
I edited this so I could understand it, so see if it does what you want.
Code: 
Option Explicit
 Sub FreezerUpdate()
 Dim Freezer As String
 Dim ItemId As Long
 Dim BrandName As String
 Dim ProductName As String
 Dim Qty As String ' Delivery
 Dim Percent As String
 Dim KeepSearching As Boolean
 Dim RowNum As Long
 Dim NewItemID As Long
 Dim i As Integer
 Dim Id As Integer
 Dim NumNewStock As Integer
 Range("A2").FormulaR1C1 = _
 "=IFERROR(VLOOKUP(RC[1]&"" ""&RC[2],'Freezer Contents'!C:C[6],2,FALSE),"""")"
 Range("A2").AutoFill Destination:=Range("Table1[ItemID]")
 Range("Table1[ItemID]").Select
 i = 2
 Id = 2
    Do Until Cells(i, 2).Value = ""
        If Cells(Id, 1).Value = "" Then
        ' the below section was a recorded macro to sort a column in numeric 'smallest to largest order in order to determine last ItemID
            Sheets("Freezer Contents").Select
            ActiveWorkbook.Worksheets("Freezer Contents").ListObjects("FreezerContents"). _
            Sort.SortFields.Clear
            ActiveWorkbook.Worksheets("Freezer Contents").ListObjects("FreezerContents"). _
            Sort.SortFields.Add Key:=Range("FreezerContents[[#All],[ItemId]]"), SortOn _
            :=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With ActiveWorkbook.Worksheets("Freezer Contents").ListObjects( _
                "FreezerContents").Sort
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
            NewItemID = ActiveCell.Value + 1
            Sheets("Bought").Cells(Id, 1) = NewItemID
            Id = Id + 1
        End If
        NumNewStock = Application.CountA(Range("A:A")) ' Determines total number of rows to update into Freezer Sheet
        With Worksheets("Bought")
            Do Until .Cells(i, 1).Value = ""
                ItemId = .Cells(i, 1).Value
                BrandName = .Cells(i, 2).Value
                ProductName = .Cells(i, 3).Value
                Qty = .Cells(i, 4).Value
                Percent = .Cells(i, 5).Value
                Freezer = .Cells(i, 6).Value
                KeepSearching = True
                RowNum = 3
                With Worksheets("Freezer Contents")
                    Do Until KeepSearching = False
                        If .Cells(RowNum, 2).Value = ItemId Then
                            If .Cells(RowNum, 5).Value <> ("NA") Then
                                .Cells(RowNum, 5).Value = .Cells(RowNum, 5).Value + Qty
                                .Cells(RowNum, 6).Value = .Cells(RowNum, 6).Value + Percent
                            End If
                        ElseIf .Cells(RowNum, 2).Value = ItemId Then
                            If .Cells(RowNum, 6).Value = ("NA") Then
                                KeepSearching = False
                            End If
                        ElseIf .Cells(RowNum, 1).Value = "" Then
                            Do Until KeepSearching = False
                                .Cells(RowNum, 2).Value = NewItemID
                                .Cells(RowNum, 3).Value = BrandName
                                .Cells(RowNum, 4).Value = ProductName
                                .Cells(RowNum, 5).Value = Qty
                                .Cells(RowNum, 6).Value = Percent
                                .Cells(RowNum, 7).Value = Freezer
                                .KeepSearching = False
                                End
                                RowNum = RowNum + 1
                            Loop
                        End If
                    Loop
                End With
                i = i + 1
            Loop ' Exterior Loop for the Bought sheet
        End With
    Loop
 Range("A2").Select
 Range("A2:F" & NumNewStock).ClearContents
 MsgBox "The Freezer Contents has been updated"
 End Sub
 
Upvote 0
thanks for all the suggestions and amazingly quick feedback, ive tried each one of your guys suggestions , code revamps/ but seem to still be missing an IF statement somewhere.

i cant thank you enough, its currenlty 00:20 UK time so i think i will review whats here again tomorrow just incase its my brain needing some renewal.

Regardless of the non solution yet, Im amazed, very helpful and thankyou!

Speak tommorrow no doubt :)

Peace.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
VBA 3 Column Table and Display each column separately in 3 list boxes.
Replies
2
Views
37
JulianvO
J
M
  • Solved
VBA comparing cell values dosen't work consistantly
Replies
6
Views
128
michaelehoudek
M
P
  • Question
Code used for comparison between 2 list.
Replies
3
Views
332
Tupe77
T
C
  • Question
VBA Sum and replace rather than Copy Destination
Replies
4
Views
258
Alex Blakenburg
A
G
  • Solved
Format & Protect range of cells using VBA
Replies
7
Views
322
Gajendran Yadhav
G

Forum statistics

Threads
1,215,425
Messages
6,124,825
Members
449,190
Latest member
rscraig11

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top