macro or vba or simple formula

[Excelhurtsmybrain]

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.

hope you can help thank you

 

[PawPawGary]

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

 

[MickG]

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.

[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

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

 

[Excelhurtsmybrain]

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