Compare fields in Excel Workbook on different sheets

Posted on 2009-02-18
Last Modified: 2012-05-06
I have an excel workbook.  I'm new to working with VBScript and excel workbooks but am trying to learn -- Any good sites to point to commands working with Excel Tabs/worksheets?

1) Read Serial # from Sheet2.Column1 into var strWarranty {look until no more}
2) Read Serial # from Sheet1.Column8 into var strServers
3) Compare strWarranty with strServers - If match WScript "MATCH"
     This will eventually populate Sheet1.Column9-10 with data from Sheet2.Column3-4.
     Loop through Sheet1.column8 until match {or not} is found.

  I am getting var strWarranty populated and output.
  I am NOT getting var strServers populated.  
  The inner DO WHILE is not being processed.

Workbook name  = ServerTest.xlsx
Sheet1 = Servers    (Server Inventory)  
Sheet2 = Warranty  {Dump of HP Warranty info)

Sheet1.Column8 = Serial # from Servers Sheet
Sheet2,Column1 = Serial # from Warranty Sheet

On Error Resume Next

' Declare Variables and Constants

Dim intCount1 

Dim intCount2 

Dim strWarranty

Dim strServers  

strExcelPath = "c:\ServerTest.xlsx"

Set objExcel = CreateObject("Excel.Application")

' Open specified spreadsheet and select the first worksheet.

objExcel.WorkBooks.Open strExcelPath

Set objSheet1 = objExcel.ActiveWorkbook.Worksheets(1)

Set objSheet2 = objExcel.ActiveWorkbook.Worksheets(2)

objExcel.Visible = True

WScript.Echo "Made it here..."

intCount1 = 2

intCount2 = 2

Do While objExcel.Sheets("Warranty").Cells(intCount1, 1).Value <> ""

    StrWarranty = objExcel.Sheets("Warranty").Cells(intCount1, 1).Value

	Wscript.echo "Warranty Serical Number: -->" & StrWarranty & "<--"

	Wscript.echo "                 COUNTS: -->" & intCount1 & "<-->" & intCount2 & "<--"

	Do While objExcel.Sheets("Servers").Cells(intCount2, 8).Value <> ""

		strServers = objExcel.Sheets("Servers").Cells(intCount2, 8).Value

		Wscript.echo "Server Serical Number: -->" & strServers & "<--" 

		If strServers = strWarranty then

			Wscript.echo "MATCH: Server Serical Number: -->" & StrServers & "<-->" & strWarranty & "<--"

		end if

		intCount2 = intCount2 + 1


	Wscript.Echo "intCount1: " & intCount1

	Wscript.Echo "intCount2: " & intCount2

	Wscript.Echo "<<-------------------------------------------->>"

	intCount1 = intCount1 + 1


Open in new window

Question by:MAJAEJ
    1 Comment
    LVL 24

    Accepted Solution

    On the sheet Servers, are there any blank lines in column 8?  In particular, is the first line blank?

    Just to double-check, the idea is to read a warranty number from one sheet, and then look for this number in the second sheet, and repeat for all warranty numbers?

    It will be quicker to use an Excel search to match the number, but first of all we need to establish why the inner loop isn't working - it should be unless it encounters an empty cell.

    For debugging you may want to remove the on error resume next.  It is difficult to track down errors with that enabled.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    This is an addendum to the following article: Acitve Directory based Outlook Signature ( The script is fine, and works in normal client-server domains…
    This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now