MisterProzilla
Active Member
- Joined
- Nov 12, 2015
- Messages
- 264
HI everyone,
I'm having trouble getting the code right for this one. Here's what I'm trying to do:
I have a list of staff absences; when they exceed 20 days I have a button to send them to a 'Long-term' sheet. That works fine. Now there are two possible reasons a message box might pop up: if the absence is already being tracked in the other sheet; and if they're not currently hitting the threshold for long-term absence. I've marked the bits where it's checking those criteria below as 'error-checking'.
My first problem is that both message boxes are coming up at the same time, even though it seems the first error-checking if statement should make it one or the other. Possibly because the syntax of checking for blanks is wrong. I also think I've got the order of the whole nested if statements wrong, but not sure where.
Second problem is that when I click Yes or No on the Yes/No msgbox, another OK box pops up with either 6 or 7, respectively. Where's that coming from?
Third problem is that I'd like to tidy the code up a bit, as it's got two duplicate processes. Could I use a goto command? I've no idea what the syntax is for that.
Any help would be appreciated. Cheers for looking
I'm having trouble getting the code right for this one. Here's what I'm trying to do:
I have a list of staff absences; when they exceed 20 days I have a button to send them to a 'Long-term' sheet. That works fine. Now there are two possible reasons a message box might pop up: if the absence is already being tracked in the other sheet; and if they're not currently hitting the threshold for long-term absence. I've marked the bits where it's checking those criteria below as 'error-checking'.
My first problem is that both message boxes are coming up at the same time, even though it seems the first error-checking if statement should make it one or the other. Possibly because the syntax of checking for blanks is wrong. I also think I've got the order of the whole nested if statements wrong, but not sure where.
Second problem is that when I click Yes or No on the Yes/No msgbox, another OK box pops up with either 6 or 7, respectively. Where's that coming from?
Third problem is that I'd like to tidy the code up a bit, as it's got two duplicate processes. Could I use a goto command? I've no idea what the syntax is for that.
Any help would be appreciated. Cheers for looking
Code:
Sub SendtoLTS()
Dim rng As Range
Dim ask
ask = MsgBox("This member of staff is not currently on long-term sickness. Are you sure you want to proceed?", vbYesNo)
Set rng = Range("A" & ActiveCell.Row & ":D" & ActiveCell.Row)
' **Error-checking 1: looks in a cell which returns yes if already tracked**'
If Range("W" & ActiveCell.Row).Value = "Yes" Then
MsgBox "An instance of long-term sickness beginning " & Range("D" & ActiveCell.Row).Value & " is already being tracked for " & Range("B" & ActiveCell.Row).Value & " " & Range("C" & ActiveCell.Row).Value
Else
'**Error-checking 2: looks at a cell that returns Yes if long-term, blank if not - is the "" below the correct syntax to check for blanks?**'
If Range("V" & ActiveCell.Row).Value = "" Then
MsgBox (ask)
If ask = vbYes Then
If IsEmpty(Sheet5.Range("B1048576").End(xlUp).Value) = True Then
Sheet5.Range("B1048576").End(xlUp).Resize(rng.Rows.Count, rng.Columns.Count). _
Cells.Value = rng.Cells.Value
Else
Sheet5.Range("B1048576").End(xlUp).Offset(1#).Resize(rng.Rows.Count, rng.Columns.Count). _
Cells.Value = rng.Cells.Value
End If
End If
'** Runs if error checks don't trigger. Can I use goto to get here instead of duplicating the above? **'
Else
If IsEmpty(Sheet5.Range("B1048576").End(xlUp).Value) = True Then
Sheet5.Range("B1048576").End(xlUp).Resize(rng.Rows.Count, rng.Columns.Count). _
Cells.Value = rng.Cells.Value
Else
Sheet5.Range("B1048576").End(xlUp).Offset(1#).Resize(rng.Rows.Count, rng.Columns.Count). _
Cells.Value = rng.Cells.Value
End If
End If
End If
End sub