Test Data Validation

**maheshdalwani** · 10-28-2010, 04:41 PM

Hi Gurus,
I am looking to automate few of my test scenarios as I expect them to grow in the coming days.I am basically looking to automate the data validation process coming from the legacy system.the attach excel file has two sheet.Sheet1 has data and sheet2 will have the testing steps.I am looking forward to have a button on sheet1 which when press should do all the validations mentioned in sheet2.The test scenarios on sheet2 are not limited and can grow based on the source system and also the data in sheet1 is not fixed. In the sample sheet there are 6 test scenarios..

what I like from the program is as follows..
1.To have separate functions for each test scenario.This will help me in reusing in separate sheet.
2.Button on Sheet1, which would be based on legacy system.So if I have 3 legacy system I would prefer to have three excel file each file have legacy system specific validations.

Scenario 1:The code would scroll down record by record to the last record and would try to compare if the key for event1 AA1 is correct by comparing it with the master sheet.If pass/fail the column K is updated with Pass.this continues till the end of the record.
At the end of the loop it should update Test sheet with completed(yes),Completion date(date), and the status,pass - if all the records are passed, fail - if any of the records fail or all the records failed validation.

Scenario 2: The code would scroll down record by record to the last record and would try to compare if the key for event1 AA2 is correct by comparing it with the master sheet.If pass/fail the column L is updated with Pass.this continues till the end of the record.
At the end of the loop it should update Test sheet with completed(yes),Completion date(date), and the status,pass - if all the records are passed, fail - if any of the records fail or all the records failed validation.

Scenario 3: The code would scroll down record by record to the last record and would try to compare if the key for event1 AA1 is correct by comparing it with the master sheet.This time it would be for reverse order.If pass/fail the column M is updated with Pass.this continues till the end of the record.
At the end of the loop it should update Test sheet with completed(yes),Completion date(date), and the status,pass - if all the records are passed, fail - if any of the records fail or all the records failed validation.

Scenario 4: The code would scroll down record by record to the last record and would try to compare if the key for event1 AA2 is correct by comparing it with the master sheet.This time it would be for reverse order.If pass/fail the column N is updated with Pass.this continues till the end of the record.
At the end of the loop it should update Test sheet with completed(yes),Completion date(date), and the status,pass - if all the records are passed, fail - if any of the records fail or all the records failed validation.

Scenario 5: The code would scroll down record by record to the last record and would try to if all the columns for code are not blank . If pass/fail the column N is updated with Pass.this continues till the end of the record.
At the end of the loop it should update Test sheet with completed(yes),Completion date(date), and the status,pass - if all the records are passed, fail - if any of the records fail or all the records failed validation.

Scenario 6: The code would scroll down record by record to the last record and would try to if all the columns for pcode are blank . If pass/fail the column M is updated with Pass.this continues till the end of the record.
At the end of the loop it should update Test sheet with completed(yes),Completion date(date), and the status,pass - if all the records are passed, fail - if any of the records fail or all the records failed validation.

I know I am asking too much, but I will try to write down other scenarios by my own, if some helps me with scenario 1,Scenario3 and scenario 5.

File attached for reference

Thanks
Mahesh

**rylo** · 10-28-2010, 07:35 PM

Hi

Can you clarify the event1, event2 and key relationships? You refer to event1 as AA1, but the data file has event1 as AA, event2 as AA1 and Key as 101. So what is scenario 1 really checking? Refer to cell references to clarify.

rylo

**maheshdalwani** · 10-29-2010, 10:12 AM

Hi Rylo,
The scenario1 will take the value of the key and event2 value from the data sheet and compare it against the master sheet to verify if the key value used for AA1 is correct or not.For example trhe macro will pick AAI and it corresponding key value 101.This will be compared to the master sheet as

    
if sheet1.event = 'AA1'
     for loop for the getting the value from sheet2..
       if   AA1 value found in sheet2 then
          if sheet1.key = sheet2.key then
           update the sheet1.t1="pass"
         else
           update sheet1.t1="fail"
        end if
      end if
next

I hope the pseudo logic should help you to indentify what I want to achieve from this code.

Thanks
Mahesh Kumar

**rylo** · 10-31-2010, 06:09 PM

HI

OK, so if test 1 result in K2 is making sure that the key in J2 is correct for the Event in C2, then what is test 2 checking? And test 3 etc. Use cell references to clarify.

rylo