macro or vba or simple formula

Excelhurtsmybrain

New Member
Joined
Nov 29, 2009
Messages
17
ive looked at so many videos and im still pretty confused at which functions would work best for me right now. (this is for university by the way) i have a hire system set up what i have to do is set a list of different bikes to choose from and each bike is charged at a different price depending on the time of day so it looks somthing like this:

cycle type** hire duration** hire charge

buggy ** halfday 0r fullday** ????????
tandem**
kids bike**

i put the bikes and hire duration in a combo box so they scroll down but i need to find the hire charge depending on which bike i select and which duration i select.

of course there is a table with all these values looks similar to this:


cyle type** half day ** full day

buggy **12** 24
tandem ** 8** 16
kids bike **5** 10



the table is where i got my bikes and half day and full day values
im not asking you to do it for me im just confused as to what function i should use i tried vlookup and im now researching match and offset but i figured asking is much easier. :confused:

hope you can help thank you
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
I placed your table of rates in cells G2:I4 and then used the following formula to determine the rate:

=IF(B2="half",VLOOKUP(A2,$G$2:$I$4,2,0),VLOOKUP(A2,$G$2:$I$4,3,0))

Not fancy but it works.

Hope this helps,
Gary
 
Upvote 0
Hi, This is a possible option.
If your data sheet is set out as below with Combobox1 (ActiveX) in the sheet then Right Click your sheet tab
Select view code ,(VB Window appears).
Paste the code below into the VB Window.
Close VB Window.
Select another sheet then Return to the Original . Your Combobox should be filled.
When you select an option the Combobox will show that option, Plus the Price.
This may be overkill , because you, could just enter al the Bikes/Times/ prices in the Combobox !!!!., But you may wish to just return the price, which could be arranged.

Code:
[COLOR=royalblue][B]Row No [/B][/COLOR][COLOR=royalblue][B]Column(A) [/B][/COLOR][COLOR=royalblue][B]Column(B) [/B][/COLOR][COLOR=royalblue][B]Column(C) [/B][/COLOR]
1.      "Bikes"    Half Day   Full day  
2.      Bike       3          6         
3.      Buggy      4          8         
4.      Tricycle   5          10        
5.      Tandem     2          4
Code:
Private [COLOR=navy]Sub[/COLOR] ComboBox1_Change()
[COLOR=navy]Dim[/COLOR] Temp [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, Term [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]If[/COLOR] InStr(ComboBox1, "/") > 0 [COLOR=navy]Then[/COLOR]
    [COLOR=navy]If[/COLOR] ComboBox1 <> ComboBox1.List(0) [COLOR=navy]Then[/COLOR]
        Temp = Left(ComboBox1, InStr(ComboBox1.Value, "/") - 2)
            Term = Mid(ComboBox1, InStr(ComboBox1.Value, "/") + 2, 1)
    [COLOR=navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
        [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
            [COLOR=navy]If[/COLOR] Dn.Value = Temp [COLOR=navy]Then[/COLOR]
                [COLOR=navy]If[/COLOR] Term = "H" [COLOR=navy]Then[/COLOR]
                    ComboBox1.Value = Temp & " (" & Term & ") " & " Price:- " & Format(Dn.Offset(, 1), "£0.00")
                    [COLOR=navy]Exit[/COLOR] [COLOR=navy]Sub[/COLOR]
                [COLOR=navy]ElseIf[/COLOR] Term = "F" [COLOR=navy]Then[/COLOR]
                    ComboBox1.Value = Temp & " (" & Term & ") " & " Price:-  " & Format(Dn.Offset(, 2), "£0.00")
                    [COLOR=navy]Exit[/COLOR] [COLOR=navy]Sub[/COLOR]
                [COLOR=navy]End[/COLOR] If
            [COLOR=navy]End[/COLOR] If
        [COLOR=navy]Next[/COLOR] Dn
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] If
[a1].Select
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Private [COLOR=navy]Sub[/COLOR] Worksheet_Activate()
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, c
[COLOR=navy]Dim[/COLOR] Ray
c = 1
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    ReDim Ray(1 To Rng.Count * 2 + 1)
 
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
        [COLOR=navy]If[/COLOR] Dn.row = 1 [COLOR=navy]Then[/COLOR]
            Ray(1) = "Bikes"
        [COLOR=navy]Else[/COLOR]
            c = c + 1
            Ray(c) = Dn.Value & " / " & "H-Day"
            c = c + 1
            Ray(c) = Dn.Value & " / " & "F-Day"
        [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]With[/COLOR] ComboBox1
    .List = Application.Transpose(Ray)
    .ListIndex = 0
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
 
Upvote 0
Thanks for actually taking you time to help guys. im a learning novie at the moment so i dont know if my explanaition are clear

so i hosted pics of the sheet and colour coded it

image 1: this should be the table where i got all my values from and the table i think i need to get the hire charges. the values in my scroll down menu come from here

image2:
blue: values are refrenced from the combo boxes and the check box aswell as the outcome of the hire charge e.g. a15 (=a9). so when ever the combo boxes is changed the value changes and when the hire charge is listed above it also shows below in the hire charge cell

red: these are the 2 comboboxes/list. so when i click i scroll down i pick a value e.g. type of bike, hire duration

green: the should be the hire charge value based on which bike i picked from the list in red and which hire duration
(bare in mind as the table shows the 2 different hire durations are what determin the bike price)

brown: this is extr i just need a button that puts a number in the box and resets the whole sheet for a new transaction entry

i hope i made this clear and i repeat again

i dotn want to ask to much of anyone so if i can just be told the methods i need il do the research and go ahead and do them thank you for taking your time to help

image 1:http://img694.imageshack.us/img694/6467/image2vq.jpg
image 2: http://img30.imageshack.us/img30/4007/imagedt.jpg
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top