+ Reply to Thread
Results 1 to 2 of 2

Zip Code Search - End If Block Error

Hybrid View

  1. #1
    Registered User
    Join Date
    05-10-2014
    MS-Off Ver
    Excel 2003
    Posts
    1

    Lightbulb Zip Code Search - End If Block Error

    I have a list of zip codes and a worksheet (main page) with a search box. I have a function to check the zip code is valid (it works so I haven't pasted below). If it is valid but not in my list, a message appears to the right of the search box. If it is in the list I want it to paste into a blank sheet where a lookup returns text from a hidden worksheet which is the cut and paste to the main page. I just can't seem to get it to work, I keep getting an end if block if error. Would really appreciate is anyone could help?

    Thanks in advance - this is what I have:

    Private Sub Search_Click()
    Sheets("Main Page").Unprotect ("checking zip code")
    Sheets("Blank").Unprotect ("checking zip code")
    Dim Result As String
    Dim ResultArray() As String
    Dim Height As Long
    Dim Width As Long
    Dim x As Long
    Dim Status As String

    Sheets("Blank").Activate

    blDimensioned = False
    Result = IsUSZipCode(Sheets("Main Page").SearchBox.Text)

    If Result = "You have not entered a valid zip code" Or Result = "The zip code you have entered is too short" Or Result = "Please enter a Zip Code." Then
    MsgBox (Result)
    Else
    If Result <> "Valid" Then
    Sheets("Main Page").SearchBox.Text = Result
    End If
    ResultArray = CheckRecords(Sheets("Main Page").SearchBox.Text)

    If ResultArray(0, 0) = "False" Then
    Sheets("Main Page").Range("C23").Value = "The zip code entered is not covered"
    Sheets("Main Page").Range("R14").Font.Size = 12
    Sheets("Main Page").Range("R14:AE15").Font.FontStyle = "Bold"
    Sheets("Main Page").Range("R14:AE15").Font.Color = vbYellow
    Sheets("Main Page").Range("R14:AE15").Interior.ColorIndex = RGB(115, 188, 102)
    Sheets("Main Page").Range("R14:AE15").MergeCells = True
    Sheets("Main Page").Range("R14:AE15").WrapText = True
    Sheets("Main Page").Range("R14:AE15").HorizontalAlignment = xlCenter
    Sheets("Main Page").Range("R14:AE15").VerticalAlignment = xlCenter
    Else

    End If

    If ResultArray(0, 0) = "True" Then

    Selection.Sheets("Main Page").SearchBox.Text
    Selection.Copy
    Sheets("Blank").Paste
    Range("B1").Select
    Selection.Copy
    Columns("A:A").Select
    Selection.ClearContents
    Sheets("Main Page").Select
    Range("R1:AE100").Select
    Selection.ClearContents
    Range("R14").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("R14:AE29").Select
    Application.CutCopyMode = False
    With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = True
    With Selection.Font
    .Name = "Arial"
    .FontStyle = "Regular"
    .Size = 11
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
    Sheets("Main Page").Activate
    End If

    Sheets("Main Page").Protect ("checking zip code")
    Sheets("Blank").Protect ("checking zip code")
    End With
    End Sub

    'Function to find row of selected list box value
    Function CheckRecords(FindItem) As String()
    Dim StartRow As Integer
    Dim EndRow As Integer
    Dim CellValue As String
    Dim blDimensioned As Boolean
    Dim Values() As String

    'Set variable values
    ReDim Values(0 To 0, 0 To 0) As String
    Values(0, 0) = "False"
    blDimensioned = False

    'value needed is two. This will avoid column headers being included in matches
    StartRow = 2
    EndRow = Sheets("List of Zip Codes").Cells(Rows.Count, "A").End(xlUp).Row
    FindItem = Trim(Replace(LCase(FindItem), " ", ""))

    Do While StartRow <= EndRow
    CellValue = Trim(Replace(LCase(Sheets("List of Zip Codes").Cells(StartRow, 1).Value), " ", ""))

    If CellValue = FindItem Then
    If blDimensioned = True Then
    ReDim Preserve Values(0 To 1, 0 To UBound(Values, 2) + 1) As String
    Values(0, UBound(Values, 2)) = Sheets("List of Zip Codes").Cells(StartRow, 2).Value
    Values(1, UBound(Values, 2)) = Sheets("List of Zip Codes").Cells(StartRow, 3).Value
    Else
    'No, so dimension it and flag it as dimensioned.
    ReDim Values(0 To 1, 0 To 0) As String
    Values(0, 0) = Sheets("List of Zip Codes").Cells(StartRow, 2).Value
    Values(1, 0) = Sheets("List of Zip Codes").Cells(StartRow, 3).Value
    blDimensioned = True
    End If
    End If
    StartRow = StartRow + 1
    Loop
    CheckRecords = Values
    End Function

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Zip Code Search - End If Block Error

    Can you add code tags when posting code?
    If posting code please use code tags, see here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Error code selecting block of data from worksheet
    By Dan Vollmer in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-07-2014, 10:32 AM
  2. Replies: 2
    Last Post: 03-17-2014, 04:19 PM
  3. [SOLVED] New Error/Old Code - Run Time Error 91; Object variable or With block variable not set
    By humboldtguy in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-15-2014, 12:39 AM
  4. [SOLVED] Excel VBA error code 91: Object variable or with block variable not set
    By wilnexpc in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-24-2013, 11:46 AM
  5. [SOLVED] vba macro code error: statement invalide outside type block
    By satania in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-20-2012, 04:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1