Excel sheet doubts in vbscripting

Question

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

Tester101 · Accepted Answer · 2009-08-05 15:05:48Z

1

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

answered Aug 5, 2009 at 15:05

Tester101

8,21213 gold badges62 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

NickSentowski · Accepted Answer · 2009-08-05 14:05:30Z

0

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

edited Aug 5, 2009 at 14:05

answered Aug 5, 2009 at 13:27

NickSentowski

81814 silver badges27 bronze badges

2 Comments

maddy Over a year ago

just shud compare the values between two sheets.How would i switch the control between two sheets of the same excel sheet.

NickSentowski Over a year ago

I just edited my answer to include examples... To make writing the formula easier, you can simply type =vlookup( then click the cell containing the "known match" between the two sheets. Then hit a comma on the keyboard, navigate to the place you want to perform the lookup, and highlight it. Hit comma, type 2, type false, close parens... You're good to go.

Collectives™ on Stack Overflow

Excel sheet doubts in vbscripting

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related