Help with macro to find matching data between two worksheets

[Monomeeth]

Hello

I am working on a macro to compare two worksheets to identify matching data.
I have got this to work nicely, but am now trying to make it as user-friendly
as possible so that others can use it without modifying the code. So far I
have got the macro to present the user with three input boxes:

---The 1st asks the user to specify what text the macro will place in a
field to indicate that the record was found.

---The 2nd asks the user to select the range of cells containing the data
they are trying to match.

---The 3rd asks the user to indicate which worksheet the macro has to look
at to find any matching data.

What I am having trouble with is: (1) how to get the user to also specify
the range of cells the macro has to look at to find the data, and (2) how to
get the user to specify the column in which to place the text specified in
the 1st input box.

The code I have so far is below:

Sub FindData()

Dim c As Range
Dim findC As Variant

Response = InputBox(Prompt:="Enter message to place in Cells")

Set MyRange = Application.InputBox( _
Prompt:="Select the range of cells containing the data you are looking
for:", Type:=8)

ComparisonSheet = InputBox( _
Prompt:="Enter the name of the worksheet you wish to investigate?")

Set Sht = MyRange.Parent

For Each c In MyRange
If Not c Is Nothing Then
Set findC = ActiveWorkbook.Sheets(ComparisonSheet).Cells _
.Find(c.Value, LookIn:=xlValues)
If Not findC Is Nothing Then
' Within the quotation marks below enter the Column you want the comments to
appear in
Sht.Range("G" & c.Row).Cells.Value = Response
End If
End If
Next
Excel.Application.SendKeys Keys:="^{HOME}", Wait:=True
DoEvents
MsgBox "Investigation completed."

End Sub


As you can see, the macro looks at the entire worksheet (as specified by the
user in the 3rd input box) to find the matching data. But my preference would
be that the user can also specify the range in that worksheet. This way they
can select the entire worksheet or just a column within the worksheet.

Also, as you can tell from the above code, the macro will place the text (or
response) in the corresponding row in column G of the active worksheet. I
would like the user to be able to either specify the column in which the text
is to be placed, or to have the macro automatically place the text in the
first column after the range of data.

I hope this all makes sense.

Any help would be most appreciated as I'm at a loss.

Joe.

 

[Billy]

Hello

I am working on a macro to compare two worksheets to identify matching data.
I have got this to work nicely, but am now trying to make it as user-friendly
as possible so that others can use it without modifying the code. So far I
have got the macro to present the user with three input boxes:

---The 1st asks the user to specify what text the macro will place in a
field to indicate that the record was found.

---The 2nd asks the user to select the range of cells containing the data
they are trying to match.

---The 3rd asks the user to indicate which worksheet the macro has to look
at to find any matching data.

What I am having trouble with is: (1) how to get the user to also specify
the range of cells the macro has to look at to find the data, and (2) howto
get the user to specify the column in which to place the text specified in
the 1st input box.

The code I have so far is below:

Sub FindData()

Dim c As Range
Dim findC As Variant

Response = InputBox(Prompt:="Enter message to place in Cells")

Set MyRange = Application.InputBox( _
    Prompt:="Select the range of cells containing the data you are looking
for:", Type:=8)

ComparisonSheet = InputBox( _
    Prompt:="Enter the name of the worksheet you wish to investigate?")

Set Sht = MyRange.Parent

  For Each c In MyRange
    If Not c Is Nothing Then
      Set findC = ActiveWorkbook.Sheets(ComparisonSheet).Cells _
                  .Find(c.Value, LookIn:=xlValues)
        If Not findC Is Nothing Then
' Within the quotation marks below enter the Column you want the commentsto
appear in
           Sht.Range("G" & c.Row).Cells.Value = Response
        End If
    End If
  Next
Excel.Application.SendKeys Keys:="^{HOME}", Wait:=True
DoEvents
MsgBox "Investigation completed."

End Sub

As you can see, the macro looks at the entire worksheet (as specified by the
user in the 3rd input box) to find the matching data. But my preference would
be that the user can also specify the range in that worksheet. This way they
can select the entire worksheet or just a column within the worksheet.

Also, as you can tell from the above code, the macro will place the text (or
response) in the corresponding row in column G of the active worksheet. I
would like the user to be able to either specify the column in which the text
is to be placed, or to have the macro automatically place the text in the
first column after the range of data.

I hope this all makes sense.

Any help would be most appreciated as I'm at a loss.

Joe.

Hi Joe,

Essentially you would require 2 further inputboxes one to request the
range and one to request the column to output to.

Therefore if you set the range to equal the inputbox for the search
criteria (similar to how you have for myRange) i.e. to:

Set MySearchRange = Application.InputBox( _
Prompt:="Select the range you wish to investigate:", Type:=8)

You could then rewrite this line of code:

Set findC = ActiveWorkbook.Sheets(ComparisonSheet).Cells _
.Find(c.Value, LookIn:=xlValues)

To:

Set findC = ActiveWorkbook.Sheets(ComparisonSheet).MySearchRange_
.Find(c.Value, LookIn:=xlValues)

In terms of getting the column, you could ask them to enter the column
letter in an inputbox then rewrite this line of code:

Sht.Range("G" & c.Row).Cells.Value = Response

To

Sht.Range(myOutputColumn & c.Row).Cells.Value = Response

Overall if your looking for ease of use for the user I would move all
five inputboxes to a userform where you can request all the
information at once as opposed to five inputboxes (might be a little
annoying five popups!). If you were using a userform you could then
load all the worksheet names into a combobox and allow the user to
select one from the list - this will avoid all sorts of headaches from
manual entry errors (typos) from the user. You could also allow the
user to check a box to say put the responses in the last column and at
the same time give them the option to specify their own, the
possibilities are endless...

Regards,

James

 

[Monomeeth]

Hi James

Thanks so much for your help. I'm almost there. I have made the suggested
changes, but am having problems with how I've set MySearchRange. Not sure
what I'm doing wrong, but I keep getting a Visual Basic runtime error 438
stating "Object doesn't support this property or method". It seems to be
having problems with the following line of code:

Set findC = ActiveWorkbook.Sheets(ComparisonSheet).MySearchRange _
.Find(c.Value, LookIn:=xlValues)


My entire revised code is below:


Sub FindData2()

Dim c As Range
Dim findC As Variant

Set MyRange = Application.InputBox( _
Prompt:="Select the range of cells containing the data you are looking
for:", Type:=8)

ComparisonSheet = InputBox( _
Prompt:="Enter the name of the worksheet you wish to investigate?")

Set MySearchRange = Application.InputBox( _
Prompt:="Select the range you wish to investigate:", Type:=8)

Response = InputBox(Prompt:="Specify the comment you want to appear to
indicate the data was found:")

MyOutputColumn = Application.InputBox( _
Prompt:="Enter the alphabetical column letter(s) to specify the column
you want the message to appear.")

Set Sht = MyRange.Parent

For Each c In MyRange
If Not c Is Nothing Then

Set findC = ActiveWorkbook.Sheets(ComparisonSheet).MySearchRange _
.Find(c.Value, LookIn:=xlValues)
If Not findC Is Nothing Then
Sht.Range(MyOutputColumn & c.Row).Cells.Value = Response
End If
End If
Next
Excel.Application.SendKeys Keys:="^{HOME}", Wait:=True
DoEvents
MsgBox "Investigation completed."

End Sub


By the way, I think you're right - a user form would be a better approach.
I'll have to design one when I get a chance.

Thanks so much for your help!

Joe.

 

[Billy]

Hi James

Thanks so much for your help. I'm almost there. I have made the suggested
changes, but am having problems with how I've set MySearchRange. Not sure
what I'm doing wrong, but I keep getting a Visual Basic runtime error 438
stating "Object doesn't support this property or method". It seems to be
having problems with the following line of code:

      Set findC = ActiveWorkbook.Sheets(ComparisonSheet).MySearchRange _
                  .Find(c.Value, LookIn:=xlValues)

My entire revised code is below:

Sub FindData2()

Dim c As Range
Dim findC As Variant

Set MyRange = Application.InputBox( _
    Prompt:="Select the range of cells containing the data you are looking
for:", Type:=8)

ComparisonSheet = InputBox( _
Prompt:="Enter the name of the worksheet you wish to investigate?")

Set MySearchRange = Application.InputBox( _
    Prompt:="Select the range you wish to investigate:", Type:=8)

Response = InputBox(Prompt:="Specify the comment you want to appear to
indicate the data was found:")

MyOutputColumn = Application.InputBox( _
    Prompt:="Enter the alphabetical column letter(s) to specify thecolumn
you want the message to appear.")

Set Sht = MyRange.Parent

  For Each c In MyRange
    If Not c Is Nothing Then

      Set findC = ActiveWorkbook.Sheets(ComparisonSheet).MySearchRange _
                  .Find(c.Value, LookIn:=xlValues)
        If Not findC Is Nothing Then
           Sht.Range(MyOutputColumn & c.Row).Cells.Value = Response
        End If
    End If
  Next
Excel.Application.SendKeys Keys:="^{HOME}", Wait:=True
DoEvents
MsgBox "Investigation completed."

End Sub

By the way, I think you're right - a user form would be a better approach..
I'll have to design one when I get a chance.

Thanks so much for your help!

Joe.

Hi Joe,

Apologies, should have spotted that yesterday, MySeachRange is an
explicit range by this I mean that it already holds the workbook and
worksheet information with it (as opposed to a relative range that
could be any range on any workbook or worksheet), therefore:

Set findC = ActiveWorkbook.Sheets(ComparisonSheet).MySearchRange _
.Find(c.Value, LookIn:=xlValues)

Needs to be rewritten as:

Set findC = MySearchRange.Find(c.Value, LookIn:=xlValues)

This obviously also means you do not need to ask the user for the
sheet to compare - avoiding all those typos - as the range already
knows which sheet...

Also to make it easier I would add:

Dim MySearchRange as Range

At the top of the procedure to ensure that the code knows its a range.
Possibly being pernickety but good practice.

Finally if you do go down the route of the userform, I have tried and
failed in past to use the RefEdit control (this control allows users
to press a button to select a range, similar to if you were selecting
a range within the formula wizard), the control is known to be
extremely buggy so I would suggest adding a textbox then use the
Textbox On Enter event to hide the form and show the inputbox, upon
the user selecting ok on the inputbox load the range from the inputbox
into the textbox and reshow the form, this will give the effect of the
RefEdit control.

Regards,

James

 

[Monomeeth]

Thanks James, that solved the problem.

And thanks for the RefEdit control tip for the userform. Hopefully I'll get
a chance at some point to have a play and design it.