Posts by samuels90

    Re: Alternative To Multiple Loops


    Andy, Dave:


    Thank you for your responses.


    Dave, I will yield your advice.


    Andy, I love the brevity of the code you provided me. Do you know of a methodology for VB to count the number of rows and then input into the code you gave me?


    Cheers

    Hey everyone,


    I have written some code (see below) which appears clumsy, can anyone suggest a more efficient approach?


    Thank you for your response.


    samuels90

    Hey everyone,


    In the macro I am writing I open a pivot table which as you know creates a sheet. I would like to name that sheet. My problem is that the default name(number if you will) changes every time I run my macro so I am unable to define which sheet I want to give a name.


    Thank you in advance for feedback.

    Re: Declaring a date range as a Variable


    Richard,


    Yes, I will have a longer list. Fuel prices in my industry changes every week. My goal is to write a program that determine the fuel cost each time a vehicle starts a job. A vehicle fills up everytime it starts a job, and the fuel cost will be different based on the location and supplier. However, the constant is that all suppliers will offer an agreed rate for 1 week.


    There are other complications, but I am building the program one step at a time (i am a novice). For example, my database will also include different suppliers who will sell at different prices within the same period.


    Please be candid if my explanation is not transparent.


    Thank you for your help.


    Don

    Re: Declaring a date range as a Variable


    Richard,


    Thank you very much for your response.


    I have had a similar thought, but being a rather inept VB programmer I felt that I would run into more complications than I am capable to manage. Attached is my code and sample sheet.


    Identify Cells Between a Date Range


    Hello everyone,


    I am writing a program and I want to write.


    Code
    Dim Week1 as Date 
    
    
    Week1 = #8/31/2007# to #9/5/2007#


    Will someone let me know whether this is possible and if so, how is it written?


    Thank you.

    Re: Select Case: Assigning Cost Based On The Date Range.


    Reafidy,


    Thank you for the Forum Rules and Anatomy of A Good Thread Title, and for showing me how to present my codes.


    Is there an alternate way to write my code? I want to pick up the Week1 rate (2.2) if the date column falls within the define date range.


    Thank you again for your kindness.

    Re: Select Case: Assigning Cost Based On The Date Range.


    Turtle44,


    Thank you very much for your response.


    From my perspective the question involves using Select Case - particularly, how the statement should be written when I am trying to define a date range.


    I am new at learning to using VB and do not always know what would be most efficient. However, I consider if I had 30,000 lines of data, using VB would be more efficient and use less memory.


    Code tags - I need to learn how to use the code tags.


    I know what dates falls within Week1, I am trying to determine why my program does not work the way I coded it.


    Thank you for any further point of view you may have.

    I want to select the cost for fuel based on the date using the select case method.


    Thank you very much for your perspective.



    [ss=
    -----------A----------- ---B---- --C-- --D-- --E-- F
    1 US Air - Ground Expense
    2 Fuel
    3 Week1 Week2 Week3 Week4
    4 Shell 2.2 2.34 2.48 2.62
    5 Pacific 2.12 2.33 2.54 2.75
    6 World Fuel Service 2.23 2.46 2.69 2.92
    7 6.55 7.13 7.71 8.29
    8
    9
    10 30/08/07 0
    11 31/08/07
    12 1/09/07
    13 2/09/07
    14 3/09/07
    15 4/09/07
    16 5/09/07
    ]*[/ss]


    You may need to see attached.

    Will someone please assist me to complete this coding?

    I would like to do a lookup for a location (C23) if the date is after 7/15/06 (J23). I am not sure how the syntax for the date should be in the function.


    See example below:
    =IF(J23>=38913,VLOOKUP(C23,'Suppliers_Location Lookup'!$A$3:$B$150,2,FALSE))


    Thank you for your help.