BlueTrisch
New Member
- Joined
- Mar 2, 2016
- Messages
- 2
Hi, I'm new to the forum and this is my first post, although just reading various threads in the past helped me a lot.
But now I am struggling to find a solution and hope you can help me.
Let's say I have 3 columns:
A - start date
B - end date
C - no of working days
and about 200 rows.
It is very likely that not all cells are filled, so that there are empty cells. These empty rows will automatically be hidden.
I want to calculate the end date, after start date and no of working days are filled out. Because the cursor jumps in the next cell below, this becomes the active cell.
Therefor I used the following code:
This works fine as long as the row above the active cell is the one, where data has been entered lately.
If for example C5 gets filled with data and row 6 is empty (and therefor hidden), C7 becomes the active cell and the Offset function doesn't work anymore.
Is there a way to select the last cell above the active cell which includes data?
Thanks in advance!
BlueTrisch
But now I am struggling to find a solution and hope you can help me.
Let's say I have 3 columns:
A - start date
B - end date
C - no of working days
and about 200 rows.
It is very likely that not all cells are filled, so that there are empty cells. These empty rows will automatically be hidden.
I want to calculate the end date, after start date and no of working days are filled out. Because the cursor jumps in the next cell below, this becomes the active cell.
Therefor I used the following code:
Code:
Sub CalcDates()
Dim Date1 As Date, Date2 As Date
Dim Days1 As Integer
Dim Holiday1 As Range
'specify start date
Date1 = ActiveCell.Offset(-1, -2).Value
'specify no of working days
Days1 = ActiveCell.Offset(-1, 0).Value - 1
'specify holidays
Set Holiday1 = Sheets("Holidays").Range("Hol")
'calculate end date
Date2 = WorksheetFunction.WorkDay(Date1, Days1, Holiday1)
'fill end day-cell
ActiveCell.Offset(-1, -1).Value = Date2
End Sub
This works fine as long as the row above the active cell is the one, where data has been entered lately.
If for example C5 gets filled with data and row 6 is empty (and therefor hidden), C7 becomes the active cell and the Offset function doesn't work anymore.
Is there a way to select the last cell above the active cell which includes data?
Thanks in advance!
BlueTrisch