hello.. I have this piece of code in my sheet..
Dim result As Range
For Each result In Range("FY04_reduction_totals")
If result < 0 And result.Offset(0, -6) = "" Then
result.Offset(0, -1).Select
Range(ActiveCell, ActiveCell.Offset(0, -3)).ClearContents
result.Offset(0, -6).Select
Range(ActiveCell, ActiveCell.End(xlUp)).Select
Application.ActiveCell.Offset(1, 0).Select
ActiveCell.Value = InputBox("Please enter a description in cell " & ActiveCell.Address & " before you enter values, Thanks!")
End If
Next result
Dim result2 As Range
For Each result2 In Range("FY05_addition_totals")
If result2 > 0 And result2.Offset(0, -6) = "" Then
result2.Offset(0, -1).Select
Range(ActiveCell, ActiveCell.Offset(0, -3)).ClearContents
result2.Offset(0, -6).Select
Range(ActiveCell, ActiveCell.End(xlUp)).Select
Application.ActiveCell.Offset(1, 0).Select
ActiveCell.Value = InputBox("Please enter a description in cell " & ActiveCell.Address & " before you enter values, Thanks!" & vbCrLf & vbCrLf & "If you don't want a description, enter one here, then delete the values entered, then you can remove description!!")
End If
Next result2
Dim result3 As Range
For Each result3 In Range("FY05_deduction_totals")
If result3 < 0 And result3.Offset(0, -6) = "" Then
result3.Offset(0, -1).Select
Range(ActiveCell, ActiveCell.Offset(0, -3)).ClearContents
result3.Offset(0, -6).Select
Range(ActiveCell, ActiveCell.End(xlUp)).Select
Application.ActiveCell.Offset(1, 0).Select
ActiveCell.Value = InputBox("Please enter a description in cell " & ActiveCell.Address & " before you enter values, Thanks!")
End If
Next result3
'the next 3 ranges check to make sure
'descriptions are entered in order
'if the cell above the current cell has no
'value in it, this will
'clear current cell, move up to next one
'and tell them to
'enter deductions in order...
Dim result4 As Range
For Each result4 In Range("FY04_reduction_descriptions")
If result4 <> "" And result4.Offset(-1, 0) = "" Then
result4.Activate
Application.ActiveCell.ClearContents
MsgBox ("Please enter reductions in order. Thanks!!")
Range(ActiveCell, ActiveCell.End(xlUp)).Select
Application.ActiveCell.Offset(1, 0).Select
End If
Next result4
Dim result5 As Range
For Each result5 In Range("FY05_addition_descriptions")
If result5 <> "" And result5.Offset(-1, 0) = "" Then
result5.Activate
Application.ActiveCell.ClearContents
MsgBox ("Please enter additions in order. Thanks!!")
Range(ActiveCell, ActiveCell.End(xlUp)).Select
Application.ActiveCell.Offset(1, 0).Select
End If
Next result5
Dim result6 As Range
For Each result6 In Range("FY05_deduction_descriptions")
If result6 <> "" And result6.Offset(-1, 0) = "" Then
result6.Activate
Application.ActiveCell.ClearContents
MsgBox ("Please enter deductions in order. Thanks!!")
Range(ActiveCell, ActiveCell.End(xlUp)).Select
Application.ActiveCell.Offset(1, 0).Select
End If
Next result6
Display More
Basically what it does is require that if something is entered in a dollar amount field (column D), that a description must first be entered in column B. Also, They must enter a description in row order first as well.. For instance they cannot enter data in row 5 before row 2 etc... Now it works fine and dandy, except when the following happens.. They fill up say 10 rows of data, descriptions and values, no problem.. well now they want to remove #2 so they delete the description, well, now it wipes out everything below it too, losing everything entered.. boy i'd be pissed if this happened to me.. is there a cleaner way to write this code to prevent this?? Thanks!
Just commenting out the clearcontents line doesn't quite make it much cleaner.. cuz then they get an unnecessary msgbox... any help is appreciated! Thanks!!
Here is a copy for you to view to see what it does.. Thanks!
http://home.ripway.com/2003-11/39141/tester.xls