Improvements to code

B

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
 
S

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
 
H

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top