Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Inserting Dates-VBA (2000)

    I have attached a file that many of you have helped me with in the past. In essence, it allows me to input daily data (on the "Daily Download" worksheet) and have this data copied over to two other worksheets-one ("Daily Summary") that compiles the total daily information in summary form (i. e., total volume and average price for all volumes purchased that day, per product line), and another ("Database") that keeps the daily input in detail (that is to say, the "Daily Download" worksheet is for just that-daily info, whereas the Database worksheet compiles all of the daily data in detail.

    My question is how to insert the daily download date into the 2nd and 3rd worksheets that correspond to the worksheet's data. Stated another way, what VBA code is required to enter the daily download date (that changes daily) into the respective cells on the "Daily Summary" and "Database" worksheets. For example, the daily download date of 4/1/2002 should be entered in cell A2 on both the "Daily Summary" and "Database" worksheets, whereas the next day's date, 4/3/2002, would be entered in cell A3 on the "Daily Summary" worksheet, and cell A6 on the "Database" worksheet, etc.

    Thanks,

    Jeff
    Attached Files Attached Files

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting Dates-VBA (2000)

    See if this will do what you want:

    <pre>Option Explicit

    Private Sub CmdTransfer_Click()
    Dim oDest As Range
    Dim I As Long, J As Long
    Application.ScreenUpdating = False
    Worksheets("Daily Download").Range("B30:M30").Copy
    Set oDest = Worksheets("Daily Summary").Range("B65536").End(xlUp).Offset(1, 0)
    oDest.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, , False
    oDest.Offset(0, -1).Value = Worksheets("Daily Download").Range("C1").Value
    oDest.Offset(1, -1).Value = Worksheets("Daily Download").Range("C1").Value + 1
    I = 0
    Do While I < 65536
    For J = 0 To 11
    If Worksheets("Database").Range("B2").Offset(I, J).Value <> "" Then
    Exit For
    End If
    Next J
    If J > 11 Then
    Exit Do
    End If
    I = I + 1
    Loop
    Worksheets("Daily Download").Range("B5:M29").Copy
    Worksheets("Database").Range("B2").Offset(I, 0).PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, , False
    Worksheets("Database").Range("B2").Offset(I, -1) = Worksheets("Daily Download").Range("C1").Value
    Do While I < 65536
    For J = 0 To 11
    If Worksheets("Database").Range("B2").Offset(I, J).Value <> "" Then
    Exit For
    End If
    Next J
    If J > 11 Then
    Exit Do
    End If
    I = I + 1
    Loop
    Worksheets("Database").Range("B2").Offset(I, -1) = Worksheets("Daily Download").Range("C1").Value + 1
    Worksheets("Daily Summary").Activate
    Worksheets("Daily Summary").Range("A1").Select
    Worksheets("Database").Activate
    Worksheets("Database").Range("A1").Select
    Worksheets("Daily Download").Activate
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    End Sub
    </pre>

    Legare Coleman

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Inserting Dates-VBA (2000)

    Yes, Legare; I didn't realize my few rows of code would require that extensive of revision.
    Thanks a ton!
    Jeff

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Inserting Dates-VBA (2000)

    Legare,
    When you have the opportunity, I would love it if you explained to me what the lines of code do! It would aid me greatly in the future.
    Thanks,
    Jeff

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting Dates-VBA (2000)

    I would be happy to answer questions about specific lines of code that you do not understand. However, there are a lot of lines to try to explain all of them in detail.
    Legare Coleman

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Inserting Dates-VBA (2000)

    OK-you asked for it (VBG!!!!!)

    Attached is the VBA code you graciously supplied. I have highlighted the lines I am unfamiliar with.

    Thanks,

    Jeff
    Attached Files Attached Files

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting Dates-VBA (2000)

    Set oDest = Worksheets("Daily Summary").Range("B65536").End(xlUp).Offset(1, 0)

    Sets the object variable oDesy to the cell after the last used cell in column B on worksheet Daily Summary. Any reference to oDest will now refer to that cell.

    oDest.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, , False

    Pastes the copied range into a range the same size with the cell refered to by oDest as the upper left corner.

    oDest.Offset(0, -1).Value = Worksheets("Daily Download").Range("C1").Value

    Puts the value in cell C1 (the date) on sheet Daily Download into the cell one to the left of the cell oDest is pointing to.

    oDest.Offset(1, -1).Value = Worksheets("Daily Download").Range("C1").Value + 1

    Puts the value in cell C1 on sheet Daily Download plus 1 (the next days date) into the cell one to the left and one down from the cell oDest points to.

    I = 0

    Sets the value of I to zero.

    Do While I < 65536

    Loops through the statements between this statement and its corresponding Loop statement while I is less than 65536.

    For J = 0 To 11

    Loops through the statements between this statement and its corresponding Next statement incrementing J from zero to 11.

    If Worksheets("Database").Range("B2").Offset(I, J).Value <> "" Then

    Checks to see if the cells between cell B and Cell M on the row I rows down from B2 are empty.

    Exit For

    if any cell between B and M on this row contains a value, exit from the For loop.

    Next J

    Increments J by 1 and returns to the statement after the corresponding For statement.

    If J > 11 Then

    Checks to see if J is greater than 11. If it is, then the For loop completed without finding a value between B and M, and this row is empty and is where we want to paste the data.

    Exit Do

    If we have found an empty row, exit from the do loop to paste the data.

    End If

    Completes the corresponding If.

    I = I + 1

    Increment I to look at the next row.

    Loop

    Loop back up to the Do While to check to see if the next row is empty.

    Worksheets("Database").Range("B2").Offset(I, 0).PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, , False

    Paste the data into the range starting at the empty row found in the loops above.

    Worksheets("Database").Range("B2").Offset(I, -1) = Worksheets("Daily Download").Range("C1").Value

    Puts the date from cell C1 on Daily Download into the cell one to the left of where the first cell was pasted.

    Do While I < 65536
    For J = 0 To 11
    If Worksheets("Database").Range("B2").Offset(I, J).Value <> "" Then
    Exit For
    End If
    Next J
    If J > 11 Then
    Exit Do
    End If
    I = I + 1
    Loop

    Same loop as above to find the first empty line after the data that was just pasted.

    Worksheets("Database").Range("B2").Offset(I, -1) = Worksheets("Daily Download").Range("C1").Value + 1

    Put the next days date in the empty row one cell to the left.

    Application.CutCopyMode = False

    Turns off the marching marquee around the data that was copied.

    Application.ScreenUpdating = True

    Turns screen updating, which was turned off at the top of the routine, back on.
    Legare Coleman

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Inserting Dates-VBA (2000)

    Thank you for your detailed explanation. It will really help me in the future.
    Jeff

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Inserting Dates-VBA (2000)

    Legare,

    I inadvertantly left out the following lines of code of which I am unfamiliar:

    Private Sub CmdTransfer_Click()
    Dim oDest As Range
    Dim I As Long, J As Long
    Application.ScreenUpdating = False

    If you wouldn't mind, would you please enlighten me?

    Thanks again,

    Jeff

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting Dates-VBA (2000)

    Private Sub CmdTransfer_Click()

    Opens a Subroutine procedure which in this case is the click event for a button control. The Private means that it is only known in the module which contains it, it can not be called from other modules.

    Dim oDest As Range

    Defines a variable named oDest which will be an object variable containing a range.

    Dim I As Long, J As Long

    Defines two variables I and J which will contain long (32 bit) integer numbers.

    Application.ScreenUpdating = False

    Turns off screen updating so that any changes you make on the spreadsheets will not be changed on the screen display until you turn screen updating back on. This keeps the screen from flashing while the macro is running, and speeds it up considerably.
    Legare Coleman

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Inserting Dates-VBA (2000)

    Legare,

    Two (2) additional questions:

    1. I would like to insert two (2) additional columns for each product . The first would calculate the weighted average cost (per unit) of the total of the product purchased on that particular day, and the second would keep a running weighted average cost (per unit) of the product from the beginning of the month;

    2. How do I reference the most recent runnung weighted average cost per unit (as set forth in the second column above) from another worksheet/file? Also, how would I do it if I wanted to pull out the weighted average price of the purchases made on a particular date and the running weighted average price of the month-to-date purchases on that same date?

    Thanks for all your help.

    Jeff

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting Dates-VBA (2000)

    On which sheet are you talking about? How do you define weighted average in this case, weighted by what? How do you want to reference the "most recent runnung weighted average," in VBA code or in a formula?
    Legare Coleman

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Inserting Dates-VBA (2000)

    Legare,
    Thanks for replying. Answers to your questions:
    1. Which Sheet? The Database Worksheet;
    2. Weighted Average? Defined as total cost in $ divided by total volume;
    3. In a formula that I can use in another file.
    I will e-mail you an attachment that will show you where I would like to insert the weighted averages.
    Thanks,
    Jeff

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Inserting Dates-VBA (2000)

    <P ID="nt"><font size=-1>(No Text)</font>
    Attached Files Attached Files

  15. #15
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting Dates-VBA (2000)

    Will the formula below, entered into E2 and then filled down column E give you what you want for the average? If so, then you will need similar formula for the other columns.

    <pre>=IF(D2<>"",SUM($D$22)/SUM($B$2:B2),"")
    </pre>


    I'll have to think about how to reverence the last average from a separate file.
    Legare Coleman

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •