I Need a forumula or VBA code

[william.mcseveney]

Hi

I need a soltion as follows:

If the name in Columnn A has a corresponding blank cell in column G &
H then change the values in column I to zero that are associated with
the name in column A. Note that the number of entries associated with
each name is variable.

If you paste this into excel it will make more sense.


EMP_SHORT_NAME EMP_SENIORITY EMP_EFF_HIRE_DATE NOM_DATE DOW SEG_CODE
START_MOMENT STOP_MOMENT DURATION
Rea, Kelly 15/05/2006 02/11/2007 Fri SICS
Rea, Kelly 15/05/2006 02/11/2007 Fri SHIFT 02/11/2007 09:30
02/11/2007 14:30 300
Rea, Kelly 15/05/2006 02/11/2007 Fri PBRK1 02/11/2007 11:20
02/11/2007 11:30 10
Rea, Kelly 15/05/2006 02/11/2007 Fri PBRK2 02/11/2007 13:35
02/11/2007 13:45 10
Rea, Kelly 15/05/2006 02/11/2007 Fri BLFCHU 02/11/2007 09:30
02/11/2007 14:30 300
Rea, Kelly 15/05/2006 02/11/2007 Fri HUDD 02/11/2007 09:40
02/11/2007 09:50 10


Many thanks

W

 

[iliace]

Like this?



Public Sub ClearColumnI()
Dim iRow As Long
Dim wsh As Excel.Worksheet
Dim calcs As Excel.XlCalculation

calcs = Application.Calculation
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Set wsh = Application.ActiveSheet
With wsh
For iRow = 2 To .UsedRange.Rows.Count
If Len(.Cells(iRow, 7)) = 0 And _
Len(.Cells(iRow, 8)) = 0 Then
.Cells(iRow, 9).Value = 0
End If
Next iRow
End With

Application.Calculation = calcs
Application.ScreenUpdating = True
End Sub