Improvements to code

[Brad]

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

 

[Sam Wilson]

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

 

[Howard31]

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!