Posts by rollis13

Re: SavaAs same directory and add date

This should work for you:

SaveAsName = ThisWorkbook.Path
ThisWorkbook.SaveAs SaveAsName & "\" & "TSValues" & Format(Now, "yyyymmdd") & ".xlsx"

Re: input a time when a file is opened AND when a cell is clicked

Quote from BoredProgrammer

[...]The first piece of code will run when the worksheet is opened and it will check for today's date in the list and the cell that contains today's date will be selected.[...]

If I can help, you need to fill column A with a date, maybe all the month long.

Re: Printing spreadsheets

Why don't you imbed the red text in the buttons ?

Re: How to make the Date AND Time result to give out just the DATE without the time

Could this work:
=IF(D3>1,IF(E3 = "",INT(NOW()+TIMEVALUE("12:00")),E3),"")

Re: How to make the Date AND Time result to give out just the DATE without the time

I tried your formula, all I did was format the output cell to Date *14/03/2001, but what would happen if you change time zone ?

Re: Lookup To Read the Last Value to the Right

Try 9,9999E maybe it depends on your country format.

Re: Insert Row with Offset

Do you mean that only the data beyond the activecell has to be shifted down ?
Which version of Excel do you have ?

Re: Insert Row with Offset

Don't really understand: "new row offset one column " but:

'for new row:
ActiveCell.Offset(1, 0).EntireRow.Insert
'
'or for new column:
ActiveCell.Offset(0, 1).EntireColumn.Insert

Re: Find same name and add the offset values

Just add one line "rFoundPts = 0" at this point:

c.Offset(, 1).Value = c.Offset(, 1).Value + rfoundpts
rFoundPts = 0
End With

Re: Macro selecting "wrong" cell

You probably missed my "after clearing the range J3:J7 " (now J3:J22).

Re: Macro selecting "wrong" cell

To have your macro working (but don't ask me why) just change the position of the output after clearing the range J3:J7 and moving it elsewhere (example: 15 rows below to J18:J22).
After this for a quick try just change these two lines of the macro:

...
FindString = Range("J" & x + 15)              'Sets number to find
...
Range("A" & ActiveCell.Row).Copy Destination:=Range("K" & x +15)
...

Re: Difference between two dates in week and days

In Conditional Formatting apply Formula Is and =INT(ABS(B1-$G$4))<=14

Re: Difference between two dates in week and days

How about rewriting the formula this way:

=INT(ABS(B1-G4)/7)&" week(s), "&MOD(ABS(B1-G4),7)&" day(s)"

Re: Adding a column across multiple worksheets using VBA

You will need to change your script to something like:

Sub KFCount()
Dim KFC
Dim j
Dim c As Range
    KFC = 0
    For j = 1 To 13
        For Each c In Sheets(j).Range("B2:B50")
            If c.Value = "KF" Then
                KFC = KFC + 1
            End If
        Next c
    Next j
    Sheets("Sheet14").Range("B1").Value = KFC
End Sub

Re: For Loops

Maybe something very simple:

For x = 1 To 5
       If x = 2 Then x = x + 1
       'rest of code
   Next

Re: Start Date and Then Fill Down Column until End Date

This post should give you the right tip: http://www.ozgrid.com/forum/sh…47505&p=528370#post528370
change formula in:
=EDATE(A1+(DAY(A1+1)=1),1)-(DAY(A1+1)=1)

Re: Date cells in worksheets

It seems that your code just needed a debug, try it now:

Sub AutoDateWorkSheets()
    Dim x As Long, d As Date, c As Range
    Dim DateVal As Long
    Dim ValDate As Date
    d = Date
    d = DateSerial(2010, 12, 26)
    'Insert First Payroll date above
    For x = 1 To 52
        For Each c In Worksheets(x).Range("A10,A13,A16,A19,A22,A25,A28")
            c = d
            d = d + 1
                                                               [COLOR=red]' 4 lines deleted[/COLOR]
        Next
        Worksheets(x).Select
        Range("A10,A13,A16,A19,A22,A25,A28").Font.Bold = True
        Columns("A:A").Select
        Selection.NumberFormat = "ddd mmm/dd/yyyy"
        [COLOR=red][COLOR=black]Worksheets(x).Columns("A:A").AutoFit[/COLOR]                   [/COLOR][COLOR=red]' moved up 1 line[/COLOR]
    Next x
    Worksheets(52).Range("A28") = d
End Sub