compare values in a spreadsheet

[ronaldmacdonald]

Hi guys/girls

Does anyone have a bit of vb code that will compare corresponding values in 2 spreadsheets?

E.g:

step 1. Open spreadsheet a
step 2. Open spreadsheet b
step 3. Compare value (spreadsheet a cell b1) >
(spreadsheet b cell b1)
step 4. If step 3 true set variable counter to 1.
If step 4 false set variable counter to 0.
step 5. Close spreadsheet a and spreadsheet b

Thats the basic spec of what I would like to achieve. Im no expert on VB stuff so am struggling to know where to begin on such a function.

Cheers

Ronald

 

[xlbo]

sub chekkit()
dim aWB as workbook, bWB as workbook
application.screenupdating = false
workbooks.open("C:\a.xls&quot
set aWB = activeworkbook
workbooks.open("C:\b.xls&quot
setbWB = activeworkbook

for each c in aWB.sheets("Sheet1&quot.usedrange.cells
compAdd = c.address
if c.value > bWb.sheets("Sheet1&quot.range(compAdd).value then
'variablecounter = 1
else
'variablecounter = 0
end if
next

aWB.close savechanges:=false
bWB.close savechanges:=false
end sub

Dunno what you are wanting to do with your "variablecounter" tho. Do you need 2 different variables - 1 to count TRUE instances and 1 to count FALSE ??? What is the purpose of this. If you give more info, you get better answers Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[ronaldmacdonald]

Hi thanks for the reply, I want to check 7 cells in one spreadsheet against 7 cells in another spreadsheet. Basically if 4 or more cells are different then I want to launch an application. I will check the variable counter value is greater than 4 and then launch my app.

Ronald

 

[xlbo]

In that case, summat like this'll work:
sub chekkit()
dim aWB as workbook, bWB as workbook, checkArr as variant
checkArr = array("A1","B1","C1","D1","E1","F1","G1&quot
'assuming the cells are the same on both sheets
application.screenupdating = false
workbooks.open("C:\a.xls&quot
set aWB = activeworkbook
workbooks.open("C:\b.xls&quot
setbWB = activeworkbook
vCtr = 0
for i = 0 to ubound(checkArr)
if aWB.sheets("Sheet1&quot.range(checkArr(i)).value = bWB.sheets("Sheet1&quot.range(checkArr(i)).value then
else
vCtr = vCtr + 1
end if
next i

aWB.close savechanges:=false
bWB.close savechanges:=false
msgbox "The number of non equal cells is " & nvCtr
'You would test for vCtr >4 here
end sub
Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[ronaldmacdonald]

Thanks Geoff

I tried that procedure and get a run time error '1004'.
Apparently its an application-defined or object-defined error?

Ronald

 

[xlbo]

what line ???

You do realise that you will ahve to change the path and workbook name and maybe also the sheet names and the ranges I used were just a suggestion
Please post back with the code that is causing the error and an indicator as to whcih line it occurs on Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[ronaldmacdonald]

Geoff

The error does not specify a line - I think it must be a general error with the layout. Ive used spreadsheet a and b as well on the local C: drive so all the path information is correct... Its actually looking at 7 rows in column C.

I basically opened a blank workbook and then pasted the function into the vb script editor, pressed the run button and voila, error. Its probably something simple like missing a keyword?

Ronald

Sub chekkit()
Dim aWB As Workbook, bWB As Workbook, checkArr As Variant
checkArr = Array("C1", "C2", "C3", "C4", "C5", "C6", "C7&quot
'assuming the cells are the same on both sheets
Application.ScreenUpdating = False
Workbooks.Open ("C:\a.xls&quot
Set aWB = ActiveWorkbook
Workbooks.Open ("C:\b.xls&quot
setbWB = ActiveWorkbook
vCtr = 0
For i = 0 To UBound(checkArr)
If aWB.Sheets("sheet 1&quot.Range(checkArr(i)).Value = bWB.Sheets("sheet 1&quot.Range(checkArr(i)).Value Then
Else
vCtr = vCtr + 1
End If
Next i

aWB.Close savechanges:=False
bWB.Close savechanges:=False
MsgBox "The number of non equal cells is " & nvCtr
'You would test for vCtr >4 here
End Sub

 

[xlbo]

ok - what are you SHEET names - is it Sheet 1 or Sheet1
A 1004 error means that you are trying to reference something that isn't there
Also - the error will give you a line - if you choose DEBUG from the error message box, it'll take you to the line where the error is Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[ronaldmacdonald]

Ok, I changed workbook a to have a sheet named a and workbook b to have a sheet name b. It now opens the two spreadsheets but gives a runtime 438 error - the object doesnt support this property or method. Its vb6 so I asssume its fairly recent.

Ronald

Workbooks.Open ("C:\a.xls&quot
Set aWB = ActiveWorkbook
Workbooks.Open ("C:\b.xls&quot
setbWB = ActiveWorkbook
vCtr = 0
For i = 0 To UBound(checkArr)
If aWB.Sheets("a&quot.Range(checkArr(i)).Value = bWB.Sheets("b&quot.Range(checkArr(i)).Value Then
Else
vCtr = vCtr + 1
End If
Next i

 

[xlbo]

Are you using VB or VBA - this is a VBA forum and there may be bits of VBA that don't work the same wway in VB. I can't say for sure 'cos I've never programmed in VB

I say again - which line does the error occur on ?? Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[ronaldmacdonald]

Im using vba through Excel, Tools --> Macro --> Visual Basic Editor. Strangely when I try to identify a line there is nothing in the Debug toolbar that will take you to the offending line.

Bizzarre.

Ronald

 

[xlbo]

LOL LOL LOL LOL
I know what the error is:
setbWB = ActiveWorkbook
should be
set bWB = ActiveWorkbook

D'OH
Anyways - tip for the future. When you are in the VB Editor, select anywhere in the sub you want to check and press F8. This will highlight the start of the sub. Press F8 to go thru the sub one line at a time (per press of F8)
This is THE best way to debug troublesome code Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[ronaldmacdonald]

Thanks Geoff, that space after the set statement has sorted it. The F8 tip is good as well as it stops on the dodgy line.

Cheers

Ronald