PDA

View Full Version : Coding Assistance!



unistudent
08-19-2014, 06:53 PM
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




121441214512146

apo
08-19-2014, 09:44 PM
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
08-20-2014, 09:38 AM
Apo the post count must be a certain number to be able to include links in messages

apo
08-20-2014, 10:31 AM
;)

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

xld
08-20-2014, 11:23 AM
It's at http://www.excelforum.com/excel-programming-vba-macros/1032107-coding-assistance.html, and he got a red card for cross-posting :)

unistudent
08-20-2014, 10:46 PM
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!





12167

apo
08-21-2014, 02:06 AM
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
08-22-2014, 02:41 PM
Hats off to you!