Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Improvements to code | Excel Programming | |||
Code Improvements | Excel Programming | |||
Performance Improvements while using RTD | Excel Programming | |||
Message Box Improvements | Excel Programming | |||
ListView to Excel Code (but needs SPEED improvements) | Excel Programming |