Improvements to code
 

The below works - but if someone adds a column before "B' or a row before 3 -
this will cause problems. I know that offset could be used or perhaps using
r1c1 formating, but was wondering what some good alternative solutions would
be. I have other code that access column "F" and "AG" and would like the
solutions to handle them all.

Thanks in advance for your help....

Sub CheckdSingleDates()
Dim i As Long
Dim cntr1 As Long

Dim IMonth As Long
Dim IDay As Long

cntr1 = 0

IMonth = Month(shtInput.Range("Issdate"))
IDay = Day(shtInput.Range("issdate"))

For i = 1 To 5
Select Case shtSingleDeposits.Range("B" & 3 + i).Value
Case Is = 0
Case Is < shtInpInfo.Range("InpSingleFirst").Value
shtSingleDeposits.Range("B" & 3 + i).ClearContents
cntr1 = cntr1 + 1
Case Is shtInpInfo.Range("InpSingleFinal").Value
shtSingleDeposits.Range("B" & 3 + i).ClearContents
cntr1 = cntr1 + 1
Case Else
If Month(shtSingleDeposits.Range("B" & 3 + i).Value) <
IMonth Or Day(shtSingleDeposits.Range("B" & 3 + i).Value) < IDay Then
shtSingleDeposits.Range("B" & 3 + i).ClearContents
cntr1 = cntr1 + 1
End If
End Select
Next
If cntr1 0 Then
MsgBox ("Single Premium Date(s) have been deleted")
Module1.GotoSingle
End If
End Sub

Improvements to code
 

I think using a with statement and an offset is easiest:

Sub CheckdSingleDates()

Dim i As Long, cntr1 As Long, IMonth As Long, IDay As Long

cntr1 = 0

IMonth = Month(shtInput.Range("Issdate"))
IDay = Day(shtInput.Range("issdate"))

with shtSingleDeposits.Range("B2")
For i = 1 To 5
Select Case .offset(i,0).Value
Case Is = 0
Case Is < shtInpInfo.Range("InpSingleFirst").Value
.offset(i,0).ClearContents
cntr1 = cntr1 + 1
Case Is shtInpInfo.Range("InpSingleFinal").Value
.offset(i,0).ClearContents
cntr1 = cntr1 + 1
Case Else
If Month(.offset(i,0).Value) < IMonth Or
Day(.offset(i,0).Value) < IDay Then
.offset(i,0).ClearContents
cntr1 = cntr1 + 1
End If
End Select
Next i
end with

If cntr1 0 Then
MsgBox ("Single Premium Date(s) have been deleted")
Module1.GotoSingle
End If

End Sub

"Brad" wrote:

The below works - but if someone adds a column before "B' or a row before 3 -
this will cause problems. I know that offset could be used or perhaps using
r1c1 formating, but was wondering what some good alternative solutions would
be. I have other code that access column "F" and "AG" and would like the
solutions to handle them all.

Thanks in advance for your help....

Sub CheckdSingleDates()
Dim i As Long
Dim cntr1 As Long

Dim IMonth As Long
Dim IDay As Long

cntr1 = 0

IMonth = Month(shtInput.Range("Issdate"))
IDay = Day(shtInput.Range("issdate"))

For i = 1 To 5
Select Case shtSingleDeposits.Range("B" & 3 + i).Value
Case Is = 0
Case Is < shtInpInfo.Range("InpSingleFirst").Value
shtSingleDeposits.Range("B" & 3 + i).ClearContents
cntr1 = cntr1 + 1
Case Is shtInpInfo.Range("InpSingleFinal").Value
shtSingleDeposits.Range("B" & 3 + i).ClearContents
cntr1 = cntr1 + 1
Case Else
If Month(shtSingleDeposits.Range("B" & 3 + i).Value) <
IMonth Or Day(shtSingleDeposits.Range("B" & 3 + i).Value) < IDay Then
shtSingleDeposits.Range("B" & 3 + i).ClearContents
cntr1 = cntr1 + 1
End If
End Select
Next
If cntr1 0 Then
MsgBox ("Single Premium Date(s) have been deleted")
Module1.GotoSingle
End If
End Sub

Improvements to code
 

To solve the problem why not name the cells you're refering to for example
Rng1, Rng2... then you can use the For i as follows:

For i = 1 To 5
Select Case shtSingleDeposits.Range("Rng" & 3 + i).Value

If you name cells, then the names will refer to those cells even if its
moved around by inserting columns or rows.

Hope this helps!
--
A. Ch. Eirinberg


"Brad" wrote:

The below works - but if someone adds a column before "B' or a row before 3 -
this will cause problems. I know that offset could be used or perhaps using
r1c1 formating, but was wondering what some good alternative solutions would
be. I have other code that access column "F" and "AG" and would like the
solutions to handle them all.

Thanks in advance for your help....

Sub CheckdSingleDates()
Dim i As Long
Dim cntr1 As Long

Dim IMonth As Long
Dim IDay As Long

cntr1 = 0

IMonth = Month(shtInput.Range("Issdate"))
IDay = Day(shtInput.Range("issdate"))

For i = 1 To 5
Select Case shtSingleDeposits.Range("B" & 3 + i).Value
Case Is = 0
Case Is < shtInpInfo.Range("InpSingleFirst").Value
shtSingleDeposits.Range("B" & 3 + i).ClearContents
cntr1 = cntr1 + 1
Case Is shtInpInfo.Range("InpSingleFinal").Value
shtSingleDeposits.Range("B" & 3 + i).ClearContents
cntr1 = cntr1 + 1
Case Else
If Month(shtSingleDeposits.Range("B" & 3 + i).Value) <
IMonth Or Day(shtSingleDeposits.Range("B" & 3 + i).Value) < IDay Then
shtSingleDeposits.Range("B" & 3 + i).ClearContents
cntr1 = cntr1 + 1
End If
End Select
Next
If cntr1 0 Then
MsgBox ("Single Premium Date(s) have been deleted")
Module1.GotoSingle
End If
End Sub