Posts by GuyGadois

    Re: Find Date & Copy Row Insert Formulas Only


    Quote from Derk

    What eactly do you mean by "paste only the formulas"? Do you mean you want to have them show as formula text? Or only the cells that are actual formulas should be copied, but they should show their calculated value.


    It would help if you gave one of the versions of the code you are using now, and perhaps one of us could suggest appropriate modification.


    Derk thanks for your posting. The row I am copying and insert pasting contains text and formulas (The formulas look at the text the user entered and diplays the data). For example in A3 it says =A1+A2. Now when I copy insert a new row I want to make sure that formula in A3 comes over but not the data/text the user entered in A1 and A2. I guess I could just insert an empty line and paste the formulas in the cell but this like a patch solution. I wasn't sure if there was a solution for just bringing the formulas over to the new row.


    The macro is on my other computer, I will try to paste it in later.


    Guy

    I have a macro that searches a column for a date and then selects the appropriate date. The next step is to then copy the row and insert a new one below the copied one and paste only the formulas and none of the data. I have tried several iterations but all of the seem to paste the data too. Is what I am trying to do possible?


    Thanks,


    Guy

    Re: Report By Date Criteria



    Thank you, that works!!! the reason I had:


    and if it is a date you push the value to the current date!

    Code
    TextBox1 = Format(Date)


    Is to make sure that Textbox1 was actually a valid date. If someone accidentially added an invalid date the macro, as it stands now, breaks. I thought the code above would just format it as a Date. I have to review that.


    Thank you again,


    Guy Gadois

    Re: Report By Date Criteria


    Quote from PCI

    There is 2 issues:
    You did not named the Date column header: column_h_date
    Date in the USerForm is read as text and not number
    Note: Month can be got from the date


    PCI, thank you for your help. Did you notice that the code in this example only picks the current date? For some reason the TextBox1.value doesn't change when the user changes it. The box is enabled and is not locked. Any idea why this is the case.


    Thanks,


    Guy


    Re: Report By Date Criteria


    Well, I have narrowed the code down to the following but I can't get it to select the matching date on the spreadsheet. When the macro starts the form comes up, the user enters in the date and the macro is supposed to search column B all the way down to the end and match the date and select the cell that contains the date. It just doesn't seem to work. Any advice is greatly appreciated.


    Guy


    [hr]*[/hr] Auto Merged Post;[dl]*[/dl]I think I found the problem, but I don't know the fix. In the code I posted above, Selected_Date = TextBox1.Value gives a value of "02/02/2008" while (Cells(I, Date_Col) displays the date without the quotes. So, there is never a match since "02/02/2008" does not match 02/02/2008. How can I get this macro to give?


    Thanks,


    Guy

    Re: Report By Date Criteria


    Quote from Dave Hawley

    Sure sounds like: "Report By Date Criteria" to me.



    Humm... not sure because I am not trying to massage the data - which a pivot table essentially does. I am just trying to ill data in a spread sheet. I don't care about the presentation of it, rather just that the data shows up. To me Report by Data Criteria sounds like I am trying to create a report based on a date rather than trying to have a macro find a date and select a cell. maybe I am wrong (probably) but it sure doesn't sound the same.


    In any case, is there a reason why my code on the sample spreadsheet doesn't work?


    Thanks,


    Guy

    Selct Cell based on Date Criteria


    OK, thank you , I am a bit closer. I have taken some of your code and tried to apply it to my spreadsheet. The code doesn't seem to be working on the attachment. It never selects the correct date. Is it something to do with the formatting? Why is it not picking the correct date?


    Please see new attachment


    Thank you,


    Guy

    Re: Report By Date Criteria


    Quote from PCI

    Certainly a macro can be used to do the interface between the UserForm and the DataBase, as far as you want to fill the DataBase.
    Date is NOT an issue because whatever the display, date is a number and will be treated like that.
    For flexibility, I would suggest to give a name to each column header:
    Cells in B1 with Date contains, named Date
    Cells in C1 with Type of Ride contains, named Type_of_Ride
    Etc...


    Thanks for your feedback. Unfortuntely, I think that what my goal is is not being well explained by me and as a result the title has been changed by Admin which isn't accurate, "Report by Date Criteria".


    Also, I don't have a "Database". What I have is a listing in one column of all dates in a month. A user will fill out a form and then click submit. Now, what I need is a way/code that will match the date entered in the form with the date on the spreadsheet and then select the date on the spreadsheet.


    I want to avoid using PivotTables since they won't work well on Macs. Plus, a pivot table is more complex then I want to make this.


    Obviously I am not doing a good job with my explanation. Please let me know if you have any questions or ideas on how I accomplish this code.


    Thanks,


    Guy

    Re: Find Date Value In Column Then Paste Form Data In Corresponding Row


    Quote from Dave Hawley

    AdvancedFilter or PivotTable.


    Actually I was hoping that a macro would search column B where my list of dates are located and when the dates on the form equal the date on the list in column B it would make that cell active. Then I could make the cells to the right of the active cell equal what is on the form. I am not sure how a PivotTable relates to adding data. I always thought of it as different ways of displaying data.


    Guy

    I am trying out a new spreadsheet to log my cycling. I have attached the example so far. I am going to have a form appear and fill it out. Upon clicking 'Add' button I want a macro that will search column B (a list of dates) and match the date the users filled out on the form with the spreadsheet and then paste the corresponding form data in that row.


    How would I accomplish this? Does the formatting of the date field complicate this since on the spreadsheet I display "Saturday 1/08" and on the form the user enters in "01/08/2008"


    Any help is appreciated.


    Guy

    Is the following the most proper way to insert a copy of a row below an active cell? It seems to work but not sure it is the correct way to go about it. I have searched this board and see that there are many ways to accomplish it.


    Thanks,


    Guy


    Code
    Sub AddRow()
        Application.ScreenUpdating = False
            ActiveCell.EntireRow.Copy
            ActiveCell.EntireRow.Insert
        Application.ScreenUpdating = True
        Application.CutCopyMode = False
    End Sub

    Why is it that when I edit some cell's formulas and press enter the result is not the changed formula but the formula itself complete with the '=' sign infront of the fuormula. The work around for me is to cut the formula and paste it into a new cell then drag the old cell over the previous one I tried to edit. Any idea why this happen on some of my cells?


    Guy

    Re: Group And Ungroup With Mac Office 2004


    Quote from Domenic

    I'm using Excel v.X, so I'm not sure this is going to help. If I protect the sheet, Group/Ungroup is unavailable. If I add your code and then protect the sheet, Group/Ungroup is still unavailable.


    Ugh, I was afraid of that. Do you know any work around that will allow protecting of cells from changes while keeping the Group/Ungroup available for Macs?

    Can any Mac users out there confirm that if a sheet is protected that a user cannot group and ungroup rows using the "+" if the sheet is protected using the following...


    Code
    With Sheet1
           .Protect Password:="******", UserInterfaceOnly:=True
           .EnableOutlining = True
        End With


    It works fine on my PC Office 2003 but I hear it won't work on a Mac but I can't confirm it.


    Thanks,


    Guy

    Re: Hide Chart Upon Open


    Quote from AAE

    Simply use the sheet name.


    If the chart is embedded on sheet1 then use

    Code
    sheet1.Visible = xlSheetVeryHidden


    If the chart is on a chart sheet, say Chart1, then

    Code
    Chart1.Visible = xlSheetVeryHidden


    Sheeesh, sorry, that was too easy. Thanks for your help.


    Guy

    To hide a worksheet upon open I use:

    Code
    wsSheet.Visible = xlSheetVeryHidden


    What do I use to hide a chart which is its own worksheet?


    Thanks,


    Guy

    Re: "document Not Saved" When Saving Mac Office 2004



    Dave, thanks for your response. I don't understand why this is happening now when I have forwarded it to my Mac many times before. The name is under 254 characters, the Mac systems in newer than OS 9.X and I don't get an error message. That leaves the 'Template' one. I am not sure about if it is a template. I sure didn't create it from a template or save it any strange way. The only thing that is interesting is that the new version has a Pivot Table and maybe that is why it is not working. Is a Pivot Table know as an embedded object?


    Thanks!


    Guy

    Re: "document Not Saved" When Saving Mac Office 2004


    Quote from mikerickson

    If you have the copy you mailed yourself, try opening it in Mac, SaveAs before making changes and then change.


    I occasionaly get that. SaveAs usualy sets things right.


    Ugh, that didn't work. Nice thought though. This is very frustrating! Any other ideas. I have search the web high and low. I will try deleting the pivot table tomorrow and see if that helps when opening on the mac.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]

    Quote from Bill Rockenbach

    I don't know anything about Mac's, but just a guess. Was it a read only file?


    Does the MAc help system give tou any clue?


    Also a nice idea but it is read/write. I have searched high and low and read anything that works.


    Thanks for the idea.


    Guy

    I created a spreadsheet in Office 2003 for PC and sent it home to my Mac with Office 2004. Now, when I try to save or save as the doc on my Mac I get the prompt "Document Not Saved". I have tried saving it as a different version to no avail. Any reason why this would happen. I frequently send my work home and this is the first time that this has happened.


    Guy