Comparing fields in two Excel workbooks - #Ref errors 2

[header777]

Using Excel 2003, I am trying to record a macro in file1 that will compare the values in a certain cell with the values in a cell in another workbook (file2). I want the user to be prompted to select the file to compare.

The following code will prompt for a file but then the subsequent formula generates a #REF error. I tested the syntax of the IF function by hardcoding the filename and it works fine so long as both files are saved to the same directory (code included but commented out). However, filenames will change - and the files are not guaranteed to be in the same directory, so this is not an option. I believe my issue is how i am referencing the 2nd workbook.

I even tried populating a different cell in file1 with the name of the file to compare with (again see code that is commented out) and that didn't help (nor do I really want to implement that approach).

I've looked in depth in the threads and FAQ's on this site and on Google, but I don't think I'm phrasing my question correctly.

Sub CompareFiles4()

Dim TheCompareFile
Dim LastRow
  
'get the filename to compare with
'Sheets("Lookup List Values").Select
'Sheets("Lookup List Values").Activate
    
'With ActiveSheet
'    Range("B10").Select
'    TheCompareFile = Selection.Value
'End With

 Sheets("Final").Select
 
    'unprotect sheet
    ActiveSheet.Unprotect

 LastRow = Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row

 'insert formula to compare reports
    Range("T4:T" & LastRow) = _
    "=IF('[& TheCompareFile]summary'!RC[-18]="""",""DATA N/A"",IF(MID(RC[-19],1,7)<>MID('[& TheCompareFile]summary'!RC[-18],1,7),""Different Reviewer"",IF(RC[-6]='[& TheCompareFile]summary'!RC[-15],""OK"",'[& TheCompareFile]summary'!RC[-15])))"

  'insert formula to compare SREA and QVR reports
  '  Range("T4:T" & LastRow) = _
  '  "=IF('[176551-b.xls]summary'!RC[-18]="""",""DATA N/A"",IF(MID(RC[-19],1,7)<>MID('[176551-b.xls]summary'!RC[-18],1,7),""Different Reviewer"",IF(RC[-6]='[176551-b.xls]summary'!RC[-15],""OK"",'[176551-b.xls]summary'!RC[-15])))"
    
    ActiveWorkbook.Save
    
    'reprotect sheet
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
  
  End Sub

 

[Bong]

I'm not sure what you're trying to do, but I'm pretty sure you don't need to be using a formula to do it. You're already in VBA so why not perform the comparison in code?

From your description, you have a cell on one worksheet and you want to know if its value is the same as that of cell on another worksheet.

fname2=Application.GetOpenFilename()
Workbooks.Open (fname2)'now your 2nd workbook is open
wb2name = Workbooks(Workbooks.Count).Name'this is the wb name
val1 = thisworkbook.sheets([red]name of the sheet[/red]).cells([red]row,column index of cell[/red]).value
val2 = workbooks(wb2name).sheets([red]name of the sheet[/red]).cells([red]row,column index of cell[/red]).value
if val1=val2 then [red]whatever[/red]

_________________
Bob Rashkin

 

[header777]

thanks for the quick response and alternative suggestion bong - however I am not sure how to implement this.

What I am trying to do is 1st check for data in file2 (cell 1), then compare names (cell 2) and if both those hold true then i compare a 3rd set of cells to ensure a match. This formula is propogated for the total # of rows in file1. With my limited VBA experience, this is easy enough for me to do with the IF function. Therefore, I was just looking to prompt the user for the file to compare against and then reference that file in the IF function.

If that is not possible, or if this is a convuluted approach, then perhaps you can explain how I can use your advice to loop thru the rows and insert the "whatever" results into the original spreadsheet.

 

[Bong]

It's certainly possible. I was (and still am) having trouble parsing your formula which is undoubtably where your reference error originates. Maybe it's just me, but I would benefit from an "algorithmic" description of what you're attempting.

_________________
Bob Rashkin

 

[header777]

That is certainly more than fair.

file1.columnT has the following formula:

if file2.columnA has data in it continue, else "Data n/a"
if file1.columnA = file2.columnB continue, else "Different Reviewer"
if file1.columnN = file2.columnE "OK", else display the valude of file2.columnE

this algorithm starts in file1.row4 and needs to be propogated down to the last populated row in file1.

I know this logic works because the formula computes if I hard code the name of file2. I want that file name to be a variable that the user sets by selecting the file.

'insert formula to compare reports
    Range("T4:T" & LastRow) = _
    "=IF('[& TheCompareFile]summary'!RC[-18]="""",""DATA N/A"",IF(MID(RC[-19],1,7)<>MID('[& TheCompareFile]summary'!RC[-18],1,7),""Different Reviewer"",IF(RC[-6]='[& TheCompareFile]summary'!RC[-15],""OK"",'[& TheCompareFile]summary'!RC[-15])))"

 

[Bong]

Well, "T" - 18 is "B", not "A", assuming I follow the "s and 's and all.

Personally, I'd still use code to do the comparisons:

fname2=Application.GetOpenFilename()
Workbooks.Open (fname2)'now your 2nd workbook is open
wb2name = Workbooks(Workbooks.Count).Name'this is the wb name
set ws1=thisworkbook.sheets("summary")
set ws2=workbooks(wb2name).sheets("summary")
intStartRow = 4
intLastRow = ws1.Range("a65536").End(xlUp)
for i=intStartRow to intLastRow
  if isempty(ws2.cells(i,1) then
     ws1.cells(i,20)="Data n/a"
  else if ws1.cells(i,1)<>ws2.cells(i,2) then
     ws1.cells(i,20)="Different Reviewer"
  else if ws1.cells(i,14)=ws2.cells(i,5) then
     ws1.cells(i,20)="OK"
  else
     ws1.cells(i,20)=ws2.cells(i,5)
  end if
next

_________________
Bob Rashkin

 

[header777]

Thanks Bob! Sorry for the delay in responding. You left out two minor items which really made me work thru this code example (and hence fully understand it - sincere thanks). It needed ".Row" after the intLastRow definition and was missing a paranethesis after isempty clause.

This works great and is definitely worth two stars - one for expertise and another for your patience in sticking with me on this one.

 

[header777]

Not sure why it's not allowing me to add 2 stars - but you truly do deserve it.

 

[Bong]

That's what I get for "coding" in this text box without checking that it actually runs!

_________________
Bob Rashkin