Consulting

Results 1 to 8 of 8

Thread: Coding Assistance!

  1. #1

    Coding Assistance!

    Editor Toolbars Remove Format Paste as plain text Paste from WordFontComic Sans MSSize7 Text Color Smiley Attachments Undo Redo
    Bold Italic Underline Align Left Center Align Right Insert/Remove Numbered List Insert/Remove Bulleted List Decrease Indent Increase Indent Link Email Unlink Insert Image Wrap [QUOTE] tags around selected text Wrap [CODE] tags around selected text Wrap [HTML] tags around selected text Wrap [PHP] tags around selected text
    Table Table Properties Delete Table Insert Row Before Insert Row After Delete Rows Insert Column Before Insert Column After Delete Columns Subscript Superscript Insert Horizontal Line Wrap [Formula] tags around selected text
    Hi I am trying to write code in VBA for a very basic kind of excel spreadsheet that can recommend the most suitable mobile tariff offer. It collects brief data from users on general things like monthly talktime and text messages sent (through a user form). And then searches it against some listed offers to work out the best deal. Best to not cause any confusions, I am including screenshots of the user form and worksheet showing a list of mobile deals. Any assistance with writing a properly functioning code will be of great help!(I had thought of using for-next loops, if statements, object variables!) Everything will be okay in the end. If its not okay, its not the end.
    Auto-Saved
    Userform.jpguserform1.jpgList of tariff.jpg

  2. #2
    VBAX Regular
    Joined
    Jul 2013
    Posts
    56
    Location
    Hi..

    Please don't forget to include any links to cross posts.. such as the one on Excelforum..


    Also.. as pointed out on that forum.. your thread title is bit lacking in description... I mean.. writing "Coding Assistance" at a VBA coding forum does not really tell anyone more than they already know..

    Note: I couldn't post the link to the crosspost... some warning message displayed..?

  3. #3
    Apo the post count must be a certain number to be able to include links in messages

  4. #4
    VBAX Regular
    Joined
    Jul 2013
    Posts
    56
    Location


    Thankyou... that brought a smile.. anyway... did you get my 2 points above about cross posting and useful thread titles..?

    Attach a Workbook (images are undesirable for a number of reasons).. and I will give it a go in the morning..

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    It's at http://www.excelforum.com/excel-prog...ssistance.html, and he got a red card for cross-posting
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    Apo the title was just like regular speech. I am attaching the workbook with tariffs for one mobile which are overall similar for the other two with different values. You have to help out to make it properly functioning.

    Xld what red card? There is no reason for it anyway. But you might want to look at it as well!





    Workbook.xlsm

  7. #7
    VBAX Regular
    Joined
    Jul 2013
    Posts
    56
    Location
    Here's a start for you..

    I had to change your sheet layout to something that likes being manipulated better.

    It will..

    1. Populate your 3 combos with sorted unique values from the required columns in Sheet1.

    2. Auto filter those columns when you press the button by the values selected in the combos.

    I guess then you can do whatever you want with the filtered results..

    There's probably more elegant ways of doing it.. but it will give you a start and it will work.



    Private Sub cmdBestPay_Click()
        With Range("A5").CurrentRegion
            .AutoFilter 1, cboMobilebrand.Value
            .AutoFilter 6, ">0", xlAnd, "<=" & cboUKMinutes.Value
            .AutoFilter 8, ">0", xlAnd, "<=" & cboUKTexts.Value
        End With
    End Sub
    
    
    Private Sub UserForm_Initialize()
        Dim it, x0
        With CreateObject("scripting.dictionary")
            For Each it In Sheets("Sheet1").Range("A6:A" & Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row)
                x0 = .Item(it.Value)
            Next
            cboMobilebrand.List = .keys
            .RemoveAll
    
    
            For Each it In Sheets("Sheet1").Range("H6:H" & Sheets("sheet1").Range("H" & Rows.Count).End(xlUp).Row)
                x0 = .Item(it.Value)
            Next
            Range("Q1").Resize(.Count).Value = Application.Transpose(.keys)
            Range("Q1").Resize(.Count).Sort [Q1], 1
            cboUKTexts.List = Range("Q1:Q" & Range("Q" & Rows.Count).End(xlUp).Row).Value
            .RemoveAll
            Columns(17).ClearContents
    
    
            For Each it In Sheets("Sheet1").Range("F6:F" & Sheets("sheet1").Range("F" & Rows.Count).End(xlUp).Row)
                x0 = .Item(it.Value)
            Next
            Range("Q1").Resize(.Count).Value = Application.Transpose(.keys)
            Range("Q1").Resize(.Count).Sort [Q1], 1
            cboUKMinutes.List = Range("Q1:Q" & Range("Q" & Rows.Count).End(xlUp).Row).Value
            Columns(17).ClearContents
            
        End With
    End Sub
    
    
    Private Sub UserForm_Terminate()
        If Sheets("Sheet1").AutoFilterMode = True Then Sheets("Sheet1").AutoFilterMode = False
    End Sub
    Attached Files Attached Files

  8. #8
    Hats off to you!

Posting Permissions

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