Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 626
  • Last Modified:

Compare fields in Excel Workbook on different sheets

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

1 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now