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
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
