explaining code

[Giggzz]

Code:
Sheet1.Range("A56").Offset(0, Range("A56", Range("V56").End(xlToLeft)).Columns.Count).Value = Format(Now, "dd/mm/yy hh:mm:ss")

Just trtying to understand what some of the code means in this

Offset(0,
Range(V56)
xlToleft

Any explaination will be helpful, Thanks

 

[DRJ]

offset moves the reference from where it is to a number of rows and columns away.

So if you are in A1 and say range("A1").offset(1,2).select
it would select one row and two columns over. In this case C2.

Range("V56") is just the cell column V row 56

xltoleft moves the referece from where it is to another cell. If the next cell to the left is blank it will skip all blanks to the next cell with a value. If the next cell has a value it will goto the last cell with a value right before the first blank.

 

[Legacy 21301]

The code is somewhat convoluted.
This does the same thing :-

Sheet1.[V56].End(xlToLeft)(1, 2) = Format(Now, "dd/mm/yy hh:mm:ss")

 

[Smitty]

Heya Ponsy!

The code is somewhat convoluted.
This does the same thing :-

Can you explain to Giggzz why? I think that's what he was looking for...

Sheet1.[V56].End(xlToLeft)(1, 2) = Format(Now, "dd/mm/yy hh:mm:ss")

It was very sweet of you to point it out though.

Smitty

 

[Legacy 21301]

Heya Ponsy!

The code is somewhat convoluted.
This does the same thing :-

Can you explain to Giggzz why? I think that's what he was looking for...

Sheet1.[V56].End(xlToLeft)(1, 2) = Format(Now, "dd/mm/yy hh:mm:ss")

It was very sweet of you to point it out though.

Smitty

The VBE Help file contains much better explanations than I could ever come up with.

 

[Giggzz]

Thank you for the code Ponsy....

Im trying to change the code so it displays the time stamp in D51:D56 then after it timestamps d56 it will continue in range F51:F56. Tried change some of the code but can't seem to get it to work. Any help?? Thanks

 

[Legacy 21301]

Thank you for the code Ponsy....

Im trying to change the code so it displays the time stamp in D51:D56 then after it timestamps d56 it will continue in range F51:F56. Tried change some of the code but can't seem to get it to work. Any help?? Thanks

I'm not sure I follow what you want to do.
Do you meant that you want to put it in D51:D56 provided those cells are empty, and if they are not empty, check every other column after column D and put it in the first available empty column?

If so, then assuming that there will be no data in columns after the latest date stamp :-

With Sheet1
    If Application.WorksheetFunction.CountA(.[D51:D56]) = 0 Then
        .[D51:D56] = Format(Now, "dd/mm/yy hh:mm:ss")
    Else
        .[IV51].End(xlToLeft)(1, 3).Resize(6) = Format(Now, "dd/mm/yy hh:mm:ss")
    End If
End With

 

[Ahnold]

Ponsy,

Thanks for butchering my code! I'm just kidding. Actually I'm still learning code and wrote the original. That's what I like about this site, I always learn something new. I like the shortcuts. This is what Giggzz is trying to do.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
        If Sheet1.[D51] = "" Then
            Sheet1.[D51] = Format(Now, "dd/mm/yy hh:mm:ss")
        Else
            If Sheet1.[D56] <> "" Then GoTo one:
            Sheet1.[D56].End(xlUp)(2, 1) = Format(Now, "dd/mm/yy hh:mm:ss")
        End If
        Exit Sub
one:    If Sheet1.[F51] = "" Then
            Sheet1.[F51] = Format(Now, "dd/mm/yy hh:mm:ss")
        Else
            Sheet1.[F56].End(xlUp)(2, 1) = Format(Now, "dd/mm/yy hh:mm:ss")
        End If
End Sub

Go ahead and butcher away! After F56 has a value, I'm not sure where she wants to go.

 

[Legacy 21301]

Dim cell As Range, c As Range
For Each cell In Sheet1.[D51:D56, F51:F56]
    If cell = "" Then
        Set c = cell
        Exit For
    End If
Next
If Not c Is Nothing Then
    c = Format(Now, "dd/mm/yy hh:mm:ss")
Else
    MsgBox "There are no blank date stamp cells available."
End If

 

[Giggzz]

THank You!! Nice Touch with the msg!