Posts by Shoo

Shoo · May 13th 2005

Re: Macro to open Word and Start Mail Merge

Hi, I have the same query - how do I set the data source as per ender's post?

Shoo · May 12th 2005

Re: Referencing worksheet into VBA

Aha, thanks, the code is complete - but how do I put it into operation so that it determines UnitHirePrice?

Code

Private Sub UnitHirePrice()


If Range("Boat").Value = Range("ItalyBoats").Value And Range("HireMonth").Value = Range("June").Value Then


Range("hirepriceunit").Value = Range("LeoJun").Value


ElseIf Range("Boat").Value = Range("ItalyBoats").Value And Range("HireMonth").Value = Range("July").Value Then


Range("HirePriceUnit").Value = Range("LeoJul").Value


ElseIf Range("Boat").Value = Range("ItalyBoats").Value And Range("HireMonth").Value = Range("August").Value Then


Range("HirePriceUnit").Value = Range("LeoAug").Value


ElseIf Range("Boat").Value = Range("SpainBoats").Value And Range("HireMonth").Value = Range("June").Value Then


Range("hirepriceunit").Value = Range("GoyJun").Value


ElseIf Range("Boat").Value = Range("SpainBoats").Value And Range("HireMonth").Value = Range("July").Value Then


Range("HirePriceUnit").Value = Range("GoyJul").Value


ElseIf Range("Boat").Value = Range("SpainBoats").Value And Range("HireMonth").Value = Range("August").Value Then


Range("HirePriceUnit").Value = Range("GoyAug").Value


ElseIf Range("Boat").Value = Range("Atlanta").Value And Range("HireMonth").Value = Range("June").Value Then


Range("hirepriceunit").Value = Range("AtlJun").Value


ElseIf Range("Boat").Value = Range("Atlanta").Value And Range("HireMonth").Value = Range("July").Value Then


Range("HirePriceUnit").Value = Range("AtlJul").Value


ElseIf Range("Boat").Value = Range("Atlanta").Value And Range("HireMonth").Value = Range("August").Value Then


Range("HirePriceUnit").Value = Range("AtlAug").Value


ElseIf Range("Boat").Value = Range("Ariel").Value And Range("HireMonth").Value = Range("June").Value Then


Range("hirepriceunit").Value = Range("SpetJun").Value


ElseIf Range("Boat").Value = Range("Ariel").Value And Range("HireMonth").Value = Range("July").Value Then


Range("HirePriceUnit").Value = Range("SpetJul").Value


ElseIf Range("Boat").Value = Range("Ariel").Value And Range("HireMonth").Value = Range("August").Value Then


Range("HirePriceUnit").Value = Range("SpetAug").Value


ElseIf Range("Boat").Value = Range("Sprite").Value And Range("HireMonth").Value = Range("June").Value Then


Range("hirepriceunit").Value = Range("SprJun").Value


ElseIf Range("Boat").Value = Range("Sprite").Value And Range("HireMonth").Value = Range("July").Value Then


Range("HirePriceUnit").Value = Range("SprJul").Value


ElseIf Range("Boat").Value = Range("Sprite").Value And Range("HireMonth").Value = Range("August").Value Then


Range("HirePriceUnit").Value = Range("SprAug").Value


ElseIf Range("Boat").Value = Range("StTropez").Value And Range("HireMonth") = Range("June").Value Then


Range("hirepriceunit").Value = Range("StTJun").Value


ElseIf Range("Boat").Value = Range("StTropez").Value And Range("HireMonth").Value = Range("July").Value Then


Range("HirePriceUnit").Value = Range("StTJul").Value


ElseIf Range("Boat").Value = Range("StTropez").Value And Range("HireMonth").Value = Range("August").Value Then


Range("HirePriceUnit").Value = Range("StTAug").Value


End Sub

Display More

Shoo · May 12th 2005

Re: Referencing worksheet into VBA

What would the opening "sub" for the below be?

Code

If Range("Boat").Value = Range("ItalyBoats").Value And Range("HireMonth") = Range("June") Then


Range("hirepriceunit") = Range("LeoJun")


ElseIf Range("Boat").Value = Range("ItalyBoats").Value And Range("HireMonth") = Range("July") Then


Range("HirePriceUnit") = Range("LeoJul")


ElseIf Range("Boat").Value = Range("ItalyBoats").Value And Range("HireMonth") = Range("August") Then


Range("HirePriceUnit") = Range("LeoAug")

Display More

Shoo · May 12th 2005

Re: Referencing worksheet into VBA

Great thanks. I will post the full code once i finished it, then tell me if it's possible as an excel formula :yikes:

Ah one more question! What do I put into the private sub line?

Shoo · May 12th 2005

Re: Referencing worksheet into VBA

Thanks for your effort in helping but I think it will be a lot cleaner and simpler in VBA, I just need to know how to call cells from the worksheet.

Shoo · May 12th 2005

Re: Referencing worksheet into VBA

Sure, thanks.

Raw Data:
ItalyBoat
GreeceBoat
FranceBoat
SpainBoat

June
July
August

ItalyJunPrice
ItalyJulPrice
ItalyAugPrice
etcetera
etcetera

OK: If ItalyBoat is selected and August is selected then the data output should be ItalyAugPrice.

Shoo · May 12th 2005

Re: Referencing worksheet into VBA

Yea I tried that, but there are too many variables. My example was just the essence of the formula.

Shoo · May 12th 2005

Hi I want to add a complex formula to my worksheet the only way to do this is by using VBA.

My question is, how do I reference my worksheet cells and ranges into VBA and how do I grammar excel VBA?

example of a function I want to emply:

The code is to determine data within "cellX"

Code

dim CellX as double


If Cell1 = Cell2 & Cell3 = Cell4


then Cellx = Cell5 


ElseIf .....

Shoo · May 12th 2005

Re: List box validation source

Edit: msg deleted

Shoo · May 11th 2005

Re: List box validation source

Solution:

=IF(SelectCountry=France,FranceBoats, IF(SelectCountry=Greece,GreeceBoats))

Shoo · May 11th 2005

=IF(SelectCountry=France,FranceBoats)
=IF(SelectCountry=Greece,GreeceBoats)

The desired effect is when selectCountry is France the options available in the list box are franceBoats but if selectCountry is Greece then the options available are Greeceboats. The equations above work as stand alones but how do I put them together in one equation?

It will be frustrating and disappointing if there is a such a short sighted limitation like this that does not allow the combination of such equations for one list box source.

Thanks.