Good tip, thanks for sharing!
Date: Sat, 30 Jan 2010 23:30:25 +0530 Subject: Re: $$Excel-Macros$$ Compare lists and return missing data From: [email protected] To: [email protected] CC: [email protected] Just wanted to share a tip here:- In order to check if the variables used in the code are empty or have some values still left within them, you can use following code just before the "End Sub" msgbox e Note:- It will show you the value of e in a pop-up message box. Best Regards, -- DILIP KUMAR PANDEY MBA-HR,B COM(Hons.),BCA Mobile: +91 9810929744 [email protected] [email protected] New Delhi - 110062 On 1/30/10, Cecilia Chiderski <[email protected]> wrote: Thanks a lot!!!! Date: Sat, 30 Jan 2010 23:23:47 +0530 Subject: Re: $$Excel-Macros$$ Compare lists and return missing data From: [email protected] To: [email protected] CC: [email protected] Dear Cecilia, I was little occupied in some other month closing deliverables at my office, hence late reply :) Yes, the code is good. VBA will release the variables after end sub. I believe e is the values which automatically finishes up during code execution and a is the range which is set as empty at the end. Let me know in case of further help is required. Best Regards, -- DILIP KUMAR PANDEY MBA-HR,B COM(Hons.),BCA Mobile: +91 9810929744 [email protected] [email protected] New Delhi - 110062 On 1/29/10, Cecilia Chiderski <[email protected]> wrote: Dilip, thanks a lot. I was working on this last night, and got this code. It seems to work... would you mind taking a look to it?? Probaby can be improved. Also, in the code, should I set e and a to empty at the end, or VBA will automatically release the variables after end sub? Sorry if it is a stupid question, but my brain is burned :) Thanks! Cecilia Sub getdifferences() Dim a, e Dim sh As Worksheet Application.DisplayAlerts = False On Error GoTo errhandle With CreateObject("Scripting.Dictionary") .CompareMode = vbTextCompare With Sheets("sheet1") a = .Range("b2", .Range("b" & Rows.Count).End(xlUp)).Value End With For Each e In a If Not IsEmpty(e) And Not .exists(e) Then .Add e, Nothing Next With Sheets("sheet1") a = .Range("a2", .Range("a" & Rows.Count).End(xlUp)).Value End With For Each e In a If .exists(e) Then .Remove (e) Next For Each sh In ThisWorkbook.Worksheets If sh.Name = "Missing" Then sh.Delete End If Next sh Sheets.Add After:=Sheets("Sheet1") ActiveSheet.Name = "Missing" Sheets("Missing").Range("A2").Resize(.Count).Value = Application.Transpose(.keys) Sheets("Missing").Range("A1") = "Missed Resources" End With Application.ScreenUpdating = True Application.DisplayAlerts = True errhandle: Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub Date: Fri, 29 Jan 2010 00:27:21 +0530 Subject: Re: $$Excel-Macros$$ Compare lists and return missing data From: [email protected] To: [email protected] CC: [email protected] Dear Cecilia, Attached file has been solved per your requirements. Get back to me in case of any queries. Best Regards, -- DILIP KUMAR PANDEY MBA-HR,B COM(Hons.),BCA Mobile: +91 9810929744 [email protected] [email protected] New Delhi - 110062 On 1/28/10, Cecilia Chiderski <[email protected]> wrote: Hi, it's me again... needing some help. Please se attached file for details. I have two lists of resources, and I need a third list showing resources in list 2 but not in 1. THANKS! Cecilia El NUEVO Internet Explorer 8 es el navegador más rápido y seguro ¡Descárgalo gratis! -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to [email protected] If you find any spam message in the group, please send an email to: Ayush Jain @ [email protected] <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,700 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe Windows 7 es fácil, cómodo y sin complicaciones. ¡Conócelo! Recibe gratis un resumen diario de tu Hotmail en tu móvil. ¡Recíbelo ya! _________________________________________________________________ ¡Seducción! 249 historias cada semana en el sitio nº1 para conseguir una cita. ¡Regístrate! http://contactos.es.msn.com/?mtcmk=015352 -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to [email protected] If you find any spam message in the group, please send an email to: Ayush Jain @ [email protected] <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,700 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
