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

Quote from PCI

Code

For some reason the TextBox1.value doesn't change when the user changes it.

Yes because you want it like that:
There is a test to check if the input is a date

Code

If IsDate(TextBox1) Then

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

Code

TextBox1 = Format(Date)

In fact I'm not completely convince by the used of the

Code

Private Sub TextBox1_Exit

I removed it in the file attached and it looks "good for me"
Note: You could short cut the loop when the date is found: use a while

Display More

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

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

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

Private Sub CommandButton1_Click()


    Application.ScreenUpdating = False


    Dim Date_Col, Date_Row, Last_Row, I As Integer
    Dim Selected_Date As Date


    Date_Col = Worksheets("2008 Log").Range("column_h_date").Column
    Date_Row = Worksheets("2008 Log").Range("column_h_date").Row
    Last_Row = Cells(65536, Date_Col).End(xlUp).Row


    Selected_Date = TextBox1.Value


    For I = Date_Row + 1 To Last_Row
        If (Cells(I, Date_Col) = Selected_Date) Then Cells(I, Date_Col).Select
    Next I
    
    Application.ScreenUpdating = True


    Unload Add_Ride_Form
    Add_Ride_Form.Hide


End Sub


Private Sub UserForm_Initialize()   
    TextBox1.Value = Date
end sub

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

Private Sub CommandButton1_Click()


    Application.ScreenUpdating = False


    Dim Date_Col, Date_Row, Last_Row, I As Integer
    Dim Selected_Date As Variant


    Date_Col = Worksheets("2008 Log").Range("column_h_date").Column
    Date_Row = Worksheets("2008 Log").Range("column_h_date").Row
    Last_Row = Cells(65536, Date_Col).End(xlUp).Row


    Selected_Date = TextBox1.Value


    For I = Date_Row + 1 To Last_Row
        If (Cells(I, Date_Col) = Selected_Date) Then Cells(I, Date_Col).Select
    Next I


    Add_Ride_Form.Hide
end sub

[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

Re: Copy Row, Insert Below

Quote from Dave Hawley

Code

Sub AddRow()
     With ActiveCell
         .EntireRow.Copy
         .EntireRow.Insert
   End With
    Application.CutCopyMode = False
End Sub

That makes sense. Thank you for the pointer!

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

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...

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:

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

Quote from Dave Hawley

"Document Not Saved" error, Saving File Created from Template.

"Document Not Saved" Saving File Created from Template

MacXL: Error Saving File to Path Longer Than 254 Characters

How to troubleshoot Macintosh system errors on OS 9.x and earlier

OFF2001: Error Messages When You Start an Office 2001 Program After Starting the Computer from a System CD

Display More

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