Posts by Gyster

  • Time calculation inconsistent

    Re: Time calculation inconsistent


    I realized the problem lies with the "hours added/24" method of adding hours to a time value in Excel. The degree of accuracy causes a value that's displayed to be slightly lower than the actual value calculated (maybe by milliseconds?). My workaround isn't elegant, but it works for this case - subtract .00001 from the quotient. I suspect this could more easily be accomplished by using the rnd function, but I haven't used it and would need to study that.

  • Time calculation inconsistent

    I'm working on a sheet that is basically a Gantt chart. The user enters a start time and duration, then using conditional formatting, the appropriate cells are filled. Time values are entered in 1/2 hour increments (from list). There are three 1/2-hour breaks (9 a.m. 11:00 a.m. and 2:00 p.m.) that need to be factored in. This is the basic rule I'm using for conditional formatting: from 6 a.m. to 9 a.m. =AND($D6<=H$4,$D6+($F6/24)>=I$4) D6 contains start time, F6 has duration and row 4 has time in 1/2 hour increments. For time periods after the breaks, I add additional checks to determine start time. I broke the rules up and tested each portion to see where things go awry and see that at around 4 p.m. the calculation returns unexpected values. I'm attaching the sheet and have highlighted the cell where I detected the first problem. It's basically saying that start time (1:30 p.m.) + duration (2.5 hours) > 4 p.m. and that shouldn't be true. I appreciate anyone that can take a look at this and provide ideas on how to correct it. It's pretty messy, because a this point, I'm just trying to get the formulas right.forum.ozgrid.com/index.php?attachment/71241/

  • User form for editing records

    Re: User form for editing records


    Thank you for this tip Roy. I have since added the tables instead of writing to the worksheet. This also added the ability to easily filter the data while working directly with the tables.

  • User form for editing records

    Re: User form for editing records


    Here's the workbook. I learned about tables self-populating after I had done a lot of work to get where I am now...that's why I needed the code to copy the formulas down...it works - not as clean as it could be, but it's ok. I don't know how much trouble it'd be to use a table instead at this point. I'm still working on the search functions. Anyway, I appreciate you taking time to help me out.


    Guy

  • User form for editing records

    Re: User form for editing records


    I've made some progress on this and now I'm able to search the database for a specific invoice and populate the userform with this data. Now I need a message box to tell the user to try a different number if they input one that doesn't exist in the database. Here's what I have so far:


    I've tried adding a message box in the If statement (as Else msgBox...) but then as soon as I click the search button, the message box is displayed even if a valid number was entered, and I cannot dismiss it. :( Any help is appreciated!

  • User form for editing records

    I have a userform to input values to a spreadsheet. The form has four types of controls – Combo boxes, Text Boxes, a Label and Date Pickers.

    The combo boxes are populated with customer names.
    The label is populated with the most recent invoice number.
    The form works wonderfully for data input, but I would like to be able to edit the data using the form. I would like the user to be able to input an invoice number and have the form populate all controls with values from that particular invoice, then edit and save the record. Kind of like a vlookup for forms I guess. If anyone can get me started I will work to figure out as much as I can on my own. Here's what I have so far:




    Help is greatly appreciated!

  • Autofill formula to next row

    Re: Autofill formula to next row


    I found this, and it seems to work:


    Code
    'Copy Formulas from V, W, X, and Y to new row
    
    With Sheets("Inv History")
        .Range("V5:V" & .Cells(.Rows.Count, "A").End(xlUp).Row).Formula = "=VLOOKUP($F5,'Cust Rates'!$A$4:D999999,2,FALSE)*O5"
        .Range("W5:W" & .Cells(.Rows.Count, "A").End(xlUp).Row).Formula = "=VLOOKUP($F5,'Cust Rates'!$A$4:E999999,3,FALSE)*N5"
        .Range("X5:X" & .Cells(.Rows.Count, "A").End(xlUp).Row).Formula = "=VLOOKUP($F5,'Cust Rates'!$A$4:F999999,4,FALSE)*(N5+O5)"
        .Range("Y5:Y" & .Cells(.Rows.Count, "A").End(xlUp).Row).Formula = "=SUM(V5:X5)"
        
    End With

  • Autofill formula to next row

    Hi and thank you in advance for taking time to try and help me. I have a user form to input data to a spreadsheet. The spreadsheet has 4 columns containing vlookups and formulas used to calculate a total. I would like the formulas to be copied to the new row each time a new record is added (button click event). Here's a screenshot to help explain:


    [ATTACH=CONFIG]69370[/ATTACH]


    Columns 22 - 24 have vlookups with formulas and column 25 just totals the other three. I manually entered the formulas into the first row and after adding the records using the form dragged them down to the rows below. The formulas work, but I would like to have them added automatically each time I add a record using the form (button click event). Notice the last record doesn't have any values. I added the record but did not drag the formulas down. Please let me know if you need more information, need to see vb, etc.


    Thanks again!