Coding Assistance!

[unistudent]

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

[apo]

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..?

[unistudent]

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

[apo]

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..

[Bob Phillips]

It's at http://www.excelforum.com/excel-prog...ssistance.html, and he got a red card for cross-posting

[unistudent]

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

[apo]

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

[unistudent]

Hats off to you!