Hi
Just testing my almost completed project and have come across two pieces of code that are proving a pain in the butt for me! I wonder if a fresh set of eyes may pick up on something i'm missing...
Basically I have to run the macro a number of times before it properly removes all the rows of data I want it to - any ideas
Sub cmdRemoveEntries_Click()
'used to automate the removal of exceptions such as Russia, Ukraine
Dim x, z As Integer
Dim cell As String
z = Module1.GlobalCount 'total number of employees to be processed
For x = 2 To z
cell = "O" & x 'O signifies the Site in which the employee is based
'feel free to add other countries/sites as they become applicable
'MS/NN/WT/VE/NS/PB = Russia, KV = Ukraine
If (Range(cell) = "WT") Or (Range(cell) = "MS") Or (Range(cell) = "NN") _
Or (Range(cell) = "KV") Or (Range(cell) = "VE") Or _
(Range(cell) = "NS") Or (Range(cell) = "PB") Then
'remove row from table
Range(cell).EntireRow.Delete
End If
Next
MsgBox "The datafeed has been sucessfully updated."
End Sub
The next problem im having is with the dates and the following peice of code. It did work originally but seems to be playing up now I think it may have something to do with leap years but not sure. Some of the dates generated are either a day early or a day late
'*******************************************************
'Calculate the employee dispatch date for email
'*******************************************************
Sub cmdEMP_email_Dispatch_date_Click()
Dim AwardDue, SenorityDate, eeDispatchDate As String
Dim x, z As Integer
z = Module1.GlobalCount
For x = 2 To z 'z = number of employees being processed
AwardDue = "G" & x 'where G is Award due i.e. 5 yrs
SenorityDate = "J" & x 'where J is Senority Date
eeDispatchDate = "L" & x 'Where L is Dispatch Date
'format the date fields to type Date (DD/MM/YY)
Range("L:L").Select
Selection.NumberFormat = "dd/mm/yy;@"
If (Range(AwardDue) = 5) Then
Range(eeDispatchDate) = Range(SenorityDate) + 1826
ElseIf (Range(AwardDue) = 10) Then
Range(eeDispatchDate) = Range(SenorityDate) + 3652
ElseIf (Range(AwardDue) = 15) Then
Range(eeDispatchDate) = Range(SenorityDate) + 5479
ElseIf (Range(AwardDue) = 20) Then
Range(eeDispatchDate) = Range(SenorityDate) + 7305
ElseIf (Range(AwardDue) = 25) Then
Range(eeDispatchDate) = Range(SenorityDate) + 9131
ElseIf (Range(AwardDue) = 30) Then
Range(eeDispatchDate) = Range(SenorityDate) + 10957
End If
Next
MsgBox "The Employee Email Dispatch date/s has now been updated."
End Sub
As always, any help is greatly appreciated.
Scott
Problems with last 2 parts of code?
Mr Teter
Hi,
Just another recommendation thats just you know.... you don't need to use
z = Module1.GlobalCount
For x = 2 To z 'z = number of employees being processed
Just use this instead
For x = 2 To Module1.GlobalCount
T. RUIZ
cell = "O" & x 'O signifies the Site in which the employee is based
'feel free to add other countries/sites as they become applicable
'MS/NN/WT/VE/NS/PB = Russia, KV = Ukraine
'remove row from table
Range(cell).EntireRow.Delete
End Select
Next
watch is
KevinBurton
Scott, I believe the answer to your first issue is that you're deleting rows but not adjusting the cursor. When you delete a row, your code is skipping to the "next" row (the one below the row that's just been deleted). I'd subtract 1 from x after deleting a row; that should fix it. (Unsolicited advice: I'd also suggest more descriptive variable identifiers than "x" and "z"
)