explaining code

Giggzz

Well-known Member
Joined
Jul 4, 2002
Messages
990
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
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
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.
 
L

Legacy 21301

Guest
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

Legend
Joined
May 15, 2003
Messages
29,536
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... :p

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

Smitty
 
L

Legacy 21301

Guest

ADVERTISEMENT

pennysaver said:
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... :p

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

Smitty

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

Giggzz

Well-known Member
Joined
Jul 4, 2002
Messages
990
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
 
L

Legacy 21301

Guest

ADVERTISEMENT

Giggzz said:
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 :-

Code:
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

Well-known Member
Joined
Feb 20, 2004
Messages
636
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.

Code:
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.
 
L

Legacy 21301

Guest
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,472
Messages
5,601,854
Members
414,479
Latest member
Beau the dog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top