+ Reply to Thread
Results 1 to 3 of 3

String search in a Range - Userform

  1. #1
    Registered User
    Join Date
    12-13-2014
    Location
    Netherlands
    MS-Off Ver
    excel 2007
    Posts
    3

    String search in a Range - Userform

    Hi guys/ladies,
    I have a userform with 4 combobox. The data selected in each combobox are then linked in a String variable (LaneID) at a button_clik event.
    This button then search LaneID in a Range on the worksheet, through a for cycle. Whene the LaneID is matched, the cell is selected, and the selection is used to calculate a formula.

    The problem is that if LaneID is not found, so it is not matched by any string in the Range, I don't have an error message! This is wrong because, in this case, I would like the user to know that LaneID is not existing, and so he has to select other values with the ComboBox.

    Thank you in advance for your help!

    Here is the code:

    ------------------------------------------
    Private Sub CalculateButton_Click()

    Dim PartialCost As Single
    Dim TotalCost As Single
    Dim LaneID As String
    Dim LaneIDcode2 As Range

    LaneID = Me.Cbo1.Value & "-A-" & Me.Cbo2.Value & "-" & Me.Cbo3.Value & "-" & Me.Cbo4.Value

    For Each Cell In Worksheets("Sheet1").Range("LaneIDcode2")
    If Cell.Value = LaneID Then
    Cell.Select
    End If
    Next

    PartialCost = (Selection.Offset(0, 1).Value * Me.Tbo1) + (Selection.Offset(0, 2).Value * Me.Tbo2.Value)

    TotalCost = PartialCost * 2

    Me.TboTotalCost.Value = CSng(TotalCost)

    'error handling if LaneID is not found in the Range?

    End Sub
    ------------------------------------------

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: String search in a Range - Userform

    Perhaps try: NOT TESTED
    Please Login or Register  to view this content.
    - Battle without fear gives no glory - Just try

  3. #3
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: String search in a Range - Userform

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

+ 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. Search A Range For Specific Text String
    By xybadog in forum Excel Formulas & Functions
    Replies: 29
    Last Post: 01-14-2015, 07:15 AM
  2. [SOLVED] Worksheet not focused when using UserForm to search string, not even with AppActivate
    By kaocko in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-06-2013, 07:25 PM
  3. [SOLVED] Search for string with repeats in the searching range
    By Hallet in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-05-2012, 05:58 PM
  4. [SOLVED] Userform Find (VLOOKUP) function search string problem
    By TalResha in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-17-2011, 04:14 AM
  5. Replies: 24
    Last Post: 04-15-2009, 09:24 AM

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