Excel macros

Repli

Im trying to write a macro in excel that compares every field in 1 column of the first sheet, with every field in 1 column of the second sheet and if there is an entry in 1 sheet that doesn't exist in another sheet, print out that entry. I have it done in C++ after exporting the data as a text file but I need to write a macro to do the same thing.
Any links to some good tutorials or anything?
Thanks

Evil Phil

Pretty much what I would do is this. We take that the workbook has the 3 default spreadsheets: Sheet1, Sheet2 and Sheet3.

So you're comparing Column A on Sheet1 to Column B on Sheet2 and the results are in Column A sheet3, to do this put the following Formula into Column A Cell1 on Sheet3 (This needs further work of its to be bi-directional).

=IF(Sheet1!A1<>Sheet2!A1, Sheet1!A2, "")

if the values are not equal (Sheet1!A1<>Sheet2!A1) then value_if_true is placed in that cell, in this case the value of Sheet1!A2 and if the value is false then "" is placed in the cell. Lookup 'If worksheet function' in the Excel help file.

Now you just have to placed this formula in all the appropriate cells, you can do this in a macro as follows.

Public Sub MacroForRepli

dim sFormula as string 
dim l as Long

   sFormula = "=IF(Sheet1!A1<>Sheet2!A1, Sheet1!A2, "")"

   ActiveWorkboox.Worksheets("Sheet3").Activate
   Range("A1").Activate
   ActiveCell.Value = sFormula 

   for l = 1 to 500 '// Nice round figure, use your own here.
      ActiveCell.OffSet(1,0).Activate '// This selects the next cell down
      ActiveCell.Value = sFormula 
  next 

End Sub

I haven't tested this code so take it as an approximation. Knowing vb helps a lot with this, I don't have any good links for you. I'm pretty much self taught.

RicardoSmith

http://mvps.org/dmcritchie/excel/excel.htm
http://www.j-walk.com/ss/excel/tips/index.htm

I've a whole bunch of links if you want me to email them to you.

Evil Phil

Could you pm them to me to Ricardo?