Cannot comprehend part of VB code

[sncb]

Hi All,

Could anyone out there 'translate' this code for me please. I dont have to use VB much much Ive been asked to make some modifications.


If IsDate(Sheets("Hold").Range("R" & Rows.Count).End(xlUp).Value) Then
LastDate = Sheets("Hold").Range("R" & Rows.Count).End(xlUp).Value
Else
LastDate = Date
End If


Thanks in adv

 

[n35]

Hi All,

Could anyone out there 'translate' this code for me please. I dont have to use VB much much Ive been asked to make some modifications.


If IsDate(Sheets("Hold").Range("R" & Rows.Count).End(xlUp).Value) Then
LastDate = Sheets("Hold").Range("R" & Rows.Count).End(xlUp).Value
Else
LastDate = Date
End If


Thanks in adv

Well without knowing the structure of your workbook, Id say it checks if last cell with a value in it in column R, of sheet "HOLD" contains a date, if it does it sets the the variable LastDate to contain this vale, if not, it sets the LastDate variable to contain the current date.

 

[Jonmo1]

This
Range("R" & Rows.Count)
Rows.Count = the number of rows available to an excel Sheet.
In xl2003 = 65536
In xl2007 = over 1 million (not sure the exact number).

so "R" & Rows.Count = R65536

Range("R65536").End(xlUp)
the red part then simulates pressing CTRL + UP arrow
So it goes to the last occupied cell in column R
then IsDate tests if that cell contains a Date
If it does, it sets the varaible LastDate to = that date

If the last cell in column R is NOT a date,
Then the variable is assigned Today's date.

 

[sncb]

Thanks both of you..

 

[sncb]

On 2nd thoughts, I dont understand this part though....

"If it does, it sets the varaible LastDate to = that date"

are you're saying, it goes from R1 down to the last row where it finds a date and then it does what?

 

[Jonmo1]

No, it goes from R65536 (in xl2003) then UP (xlUp) to the first cell it encounters that contains a value.
This is the LAST cell in the column that contains a value.