Finding the same unique identifier in multiple worksheets

[Monomeeth]

Hello

I have been using this code for about a year now, adapting it to suit
different workbooks and situations. However, I want to try and make it a
little more foolproof so that I can share it with other staff in our
organisation.

The code is as follows:

Sub RespondedToForm()
' Macro created to compare User IDs between the "Sent the Form"
' worksheet (on which this macro is run) and the "Responded to Form" worksheet

Dim c As Range
Dim findC As Variant

For Each c In ActiveSheet.Range("E2:E158")
If Not c Is Nothing Then
Set findC = Worksheets("Responded to form").Cells _
.Find(c.Value, LookIn:=xlValues)
If Not findC Is Nothing Then
ActiveSheet.Range("Q" & c.Row).Cells.Value = "Yes"
End If
End If
Next
End Sub


As you can see, this macro will check to see whether a User Id appearing in
the active sheet range E2 to E158 also appears in the "Responded to Form"
worksheet and if so, it places the text "Yes" in the corresponding row cell
at Column Q of the active worksheet.

I have always changed the ranges, worksheet names and text within the code
to suit my needs. However, I now want to be able to get other staff to use
this macro without the need for them to have to play with the code.

Ideally, I would like to design a user form that asks the user to specify
the: (i) worksheets being compared, (ii) ranges to compare, (iii) column in
which the text is to be added, (iv) actual text to be added.

I did try using a Do Until Loop with the IsEmpty command, but ran into
problems.

I'm no expert with VBA, but I know enough to be dangerous. I can also design
the user form, but getting the coding right is where I'm having problems.
However, I would be happy to consider other suggestions if this could be done
without the need of a user form, as I am unsure how transferable the user
form would be from one workbook to another when staff needed to use the macro
in different workbooks.

Hope this makes sense!

Joe.

 

[Joel]

See if this works

Sub RespondedToForm()
' Macro created to compare User IDs between the "Sent the Form"
' worksheet (on which this macro is run) and the "Responded to Form" worksheet

Dim c As Range
Dim findC As Variant

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

Set MyRange = Application.InputBox( _
Prompt:="Select Range", Type:=8)

Set Sht = MyRange.Parent

For Each c In MyRange
If Not c Is Nothing Then
Set findC = Worksheets("Responded to form").Cells _
.Find(c.Value, LookIn:=xlValues)
If Not findC Is Nothing Then
Sht.Range("Q" & c.Row).Cells.Value = Response
End If
End If
Next

End Sub

 

[Monomeeth]

Hi Joel

Many thanks for your reply. Your code was great - it gave me enough to work
with to get it closer to my ideal situation. The code now looks like:

Sub FindData()
' Macro created to check whether data located within user-defined cells in
' the active worksheet also appears on another worksheet as defined by the
user

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("Q" & c.Row).Cells.Value = Response
End If
End If
Next
Range("A1").Select
MsgBox "Investigation completed."

End Sub

What would make this perfect would be to give the user the ability to select
the column in which to put the response value. At present it is hard coded as
column Q, and this would need to be changed if the user wanted to select a
different column.

Basically, what I want is for the response text to be placed in the
corrsponding row cell of the next vacant column so that it appears at the end
of the row.

Hope this makes sense!

Joe.