Hi, You need to use an input box, not a message box.
Instead of: MsgBox ("Select Source sheet against which data will be checked") Use: Ans = Inputbox("Select Source sheet against which data will be checked") The Ans variable will contain whatever the user inputs. Remember that when asking for a column, you probably need a number, not a letter. Somehow you will have to pursuade the operator to enter 1 for Col A, or 2 for col B etc. Regards - Dave. > Date: Mon, 23 Mar 2009 14:18:08 -0700 > Subject: $$Excel-Macros$$ User input as an argument in duplicate deletion > macro > From: firstcasua...@googlemail.com > To: excel-macros@googlegroups.com > > > Hi, I'm putting together a macro that will allow a user to select two > separate workbooks and compare one column in each workbook to find any > matches across them. Where there is a match it will copy the row to a > third sheet and delete it from the original sheet, before moving on to > the next value. > > I have an input box asking the user which column number should be > searched but I don't know how to make the macro use their response in > the logic test here - "If x.Value = Sheets(1).Cells(iCtr, 1).Value > Then". Could anyone give me some pointers on that please? > > Dim iListCount As Integer > Dim iCtr As Integer > Dim LoopRange As Range > Dim HoldRng As Range > Dim MatchRow As Range > Dim SourceColumn As Integer > Dim SourceSheet As Range > Dim TargetSheet As Range > > MsgBox ("Select Source sheet against which data will be checked") > > FileName = Application.GetOpenFilename > Workbooks.Open FileName > > If FileName = "" Then End > > MsgBox ("Select Target sheet from which duplicates will be deleted") > > Set FileName = SourceSheet > > FileName2 = Application.GetOpenFilename > Workbooks.Open FileName2 > > If FileName2 = "" Then End > > Set FileName2 = TargetSheet > > FileNum = FreeFile() > > ' Get count of records to search through (list that will be deleted). > iListCount = Sheets(1).UsedRange.Rows.Count > > SourceColumn = InputBox("Enter the number of the matching column from > Left To Right, e.g 1 for A, 2 for B", "Column Selection") > > Application.ScreenUpdating = False > ' Loop through the "master" list. > For Each x In Sheets(1).UsedRange > > ' Loop through all records in the second list. > For iCtr = 1 To iListCount > ' Do comparison of next record. > ' To specify a different column, change 1 to the column number. > If x.Value = Sheets(1).Cells(iCtr, 1).Value Then > ' If match is true then delete row. > Sheets(1).Cells(iCtr, 1).EntireRow.Delete xlShiftUp > ' Increment counter to account for deleted row. > iCtr = iCtr + 1 > End If > Next iCtr > Next > Application.ScreenUpdating = True > MsgBox "All duplicates removed" > > > > > _________________________________________________________________ View photos of singles in your area. Click Here http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fdating%2Eninemsn%2Ecom%2Eau%2Fchannel%2Findex%2Easpx%3Ftrackingid%3D1046247&_t=773166080&_r=Hotmail_Endtext&_m=EXT --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---