Excel sheet doubts in vbscripting

Question

Hi all, I am creating an excel file as shown below strFileName = "c:\test.xls"

         Set objExcel = CreateObject("Excel.Application")
         objExcel.Visible = True

         Set objWorkbook = objExcel.Workbooks.Add()
         objWorkbook.SaveAs(strFileName)

         objExcel.Quit

Now after some data manipulation i populate some values into the excel sheet manually in sheet1 and sheet2 of the same excel sheet. I would be glad if anyone could tel me how to compare two values in two different sheets of the same excelk sheet For eg: sheet1 has

             **Executables**             **checkbox**
                E1                             yes
                E2                             No
                E3                             yes

and sheet2 has Executables Number of parameters input1 Input 2 E1 Send 2 4 {ENTER} Put 2 Input.xls {ENTER} Send 2 4 {ENTER} {ENTER} Now i want my vbscript shud search in sheet 1 for each yes and then come here and search in sheet2.How would i do a comparitive search on sheet1 and sheet2 in the same excel sheet files

        Thanks
        Maddy

Answer 1

Does your data need to be stored in form elements, or can you just use the spreadsheet functions? A simple VLookup formula will compare two sheets, but can only search data contained in cells.

     SHEET 1
  Boy     Age
1 Joe     13
2 Jimmy   12
3 Jack    27

In the 2nd sheet, use the following formula:

 =if(vlookup(a1,'[Sheet 1.xlsx]Sheet1'!$A$1:$B$2,2,false)=a2, "Same", "Different")

     SHEET 2
  Boy     Age   Formula
1 Joe     14    Different
2 Jimmy   12    Same
3 Jack    27.5  Different

Answer 2

What about using two sheet objects to compare the values
This will compare each cell in sheet1 to the same cell in sheet2.

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Test.xls")
Set objSheet1 = objExcel.ActiveWorkbook.Worksheets(1)
Set objSheet2 = objExcel.ActiveWorkbook.Worksheets(2)

For i = 1 To objSheet1.UsedRange.Columns.Count
    For j = 1 To objSheet1.UsedRange.Rows.Count
     if (objSheet1.Cells(j, i).Value = objSheet2.Cells(j,i).Value) Then
      'Equal do something
     else
      'Not Equal do something
     End IF
    Next
Next

objExcel.Quit
Set objSheet2 = Nothing
Set objSheet1 = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing