Please follow the table where some tasks details have been gathered.Now i am looking for any VBscript on these types of excel sheet which can check all the TCompdate column,If it is found there is no value in that column then its related two columns say here T,TSdate should be blank.
Input Table
PID T1 T1Sdate T1Compdate T2 T2Sdate T2Compdate T3 T3Sdate T3Compdate
10 A 2/5/11 4/5/11 B 06/09/12 C 11/11/11
11 A 2/5/11 B 06/09/12 8/8/10 C 11/11/11 5/4/11
12 A 2/5/11 B 06/09/12 8/8/10 C 11/11/11 5/4/11
Output Table
PID T1 T1Sdate T1Compdate T2 T2Sdate T2Compdate T3 T3Sdate T3Compdate
10 A 2/5/11 4/5/11
11 B 06/09/12 8/8/10 C 11/11/11 5/4/11
12 B 06/09/12 8/8/10 C 11/11/11 5/4/11
CODE:
Option Explicit
Dim objExcel1,objWorkbook
Dim strPathExcel1
Dim objSheet1,IntRow1
Dim Counter
Set objExcel1 = CreateObject("Excel.Application")
strPathExcel1 = "D:VATestVBSScriptsDataNullificationDataNullification.xlsx"
Set objWorkbook=objExcel1.Workbooks.open(strPathExcel1)
Set objSheet1 = objExcel1.ActiveWorkbook.Worksheets(1)
IntRow1=2
Do While objSheet1.Cells(IntRow1,1).Value <> ""
For Counter=2 to 13 Step 3
If objSheet1.Cells(IntRow1,Counter+2).Value = "" Then
objSheet1.Cells(IntRow1,Counter).Value=""
objSheet1.Cells(IntRow1,Counter+1).Value=""
End If
Next
IntRow1=IntRow1+1
Loop
'=======================
objExcel1.ActiveWorkbook.SaveAs strPathExcel1
objExcel1.Workbooks.close
objExcel1.Application.Quit
'======================
Thanks,
See Question&Answers more detail:os