We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Compare fields in Excel Workbook on different sheets

MAJAEJ
MAJAEJ asked
on
Medium Priority
636 Views
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?

Objective:  
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.

Problem:
  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
	Loop
 
	Wscript.Echo "intCount1: " & intCount1
	Wscript.Echo "intCount2: " & intCount2
	Wscript.Echo "<<-------------------------------------------->>"
	intCount1 = intCount1 + 1
Loop

Open in new window

Comment
Watch Question

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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.