code to insert dash sign

Like so:

Sub Sort()
    Application.Goto Reference:="R6C3:R2000C27"
    ActiveWorkbook.Worksheets("Access").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Access").Sort.SortFields.Add Key:=Range("C6"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Access").Sort
        .SetRange Range("C6:aa2000")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    Dim vFIND As Range, vFIRST As Range
    With ActiveWorkbook.Worksheets("Access")
        On Error Resume Next
        Set vFIND = .Range("F:F").Find("Company", LookIn:=xlValues, LookAt:=xlWhole)
        If Not vFIND Is Nothing Then
            Set vFIRST = vFIND
            Do
                .Range("Q" & vFIND.Row).Resize(, 11).Value = "-"
                Set vFIND = .Range("F:F").FindNext(vFIND)
            Loop Until vFIRST.Address = vFIND.Address
        End If
    End With

    Sheets("Search").Range("B4:C4").ClearContents
    ActiveWorkbook.Save

End Sub

Hello JBeaucaire,
Thank you. It's working fine, excellent!
Now I want a little modification in it. I have made some change in it but now it's not working. I want to do the same when the column 'f' contains the word 'Organization' and 'School'.
It's not gibving error but not giving the output.
In short if the column 'f' contains either the three of words then the dash sign should be enter in column 'q to aa'.

Please find the error and solve my problem.

Thanking you in anticipation.

Mukesh

    Dim vFIND As Range, vFIRST As Range, v As Long, MyStrings As Variant

    MyStrings = Array("Company", "Organization", "School")

    With ActiveWorkbook.Worksheets("Access")
        On Error Resume Next
        For v = LBound(MyStrings) To UBound(MyStrings)
            Set vFIND = .Range("F:F").Find(MyStrings(v), LookIn:=xlValues, LookAt:=xlWhole)
            If Not vFIND Is Nothing Then
                Set vFIRST = vFIND
                Do
                    .Range("Q" & vFIND.Row).Resize(, 11).Value = "-"
                    Set vFIND = .Range("F:F").FindNext(vFIND)
                Loop Until vFIRST.Address = vFIND.Address
                Set vFIRST = Nothing
                Set vFIND = Nothing
            End If
        Next v
    End With

Put this code into the Access Sheet module:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

For Each cell In Target
    If cell.Column = 6 Then
        Select Case cell.Value
            Case "Company", "Organization", "School", "S. S. C. board"
                Application.EnableEvents = False
                Range("Q" & cell.Row).Resize(, 9).Value = "-"
                Application.EnableEvents = True
            Case Else
                'do nothing
        End Select
    End If
Next cell

End Sub

In your original code reduces down to:

Sub Sort()
    Application.Goto Reference:="R7C3:R2000C25"
    ActiveWorkbook.Worksheets("Access").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Access").Sort.SortFields.Add Key:=Range("C7"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Access").Sort
        .SetRange Range("C7:y2000")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    ActiveWorkbook.Save
End Sub

I think that's enough for one thread.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Originally Posted by mukeshbaviskar

Thank you for your guidance. I inserted the code in my modul but it's not working.

That's not where I told you you put that macro.

Originally Posted by JBeaucaire

Put this code into the Access Sheet module:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

For Each cell In Target
    If cell.Column = 6 Then
        Select Case cell.Value
            Case "Company", "Organization", "School", "S. S. C. board"
                Application.EnableEvents = False
                Range("Q" & cell.Row).Resize(, 9).Value = "-"
                Application.EnableEvents = True
            Case Else
                'do nothing
        End Select
    End If
Next cell

End Sub

This is a self-activating macro, so it must go into the sheet itself where it is to run.

http://screencast.com/t/oaT8355e2X

You inserted a new standard code module. Why? IN the post above I told you specifically to put it into the ACCESS sheet module. I included a picture pointing directly at the correct module to open.

I apologize for not doing everything for you, following instructions is part of the learning process. You can do this.

Your Sort macro is not working any longer, go back to your original version back before we started all this.

I'll have to take your word since that workbook you uploaded still has the macro in a code module instead of the sheet module.

I see the problem. Your first sheet had "Company" but you want to type "company", not exactly the same. So, if you want the macro to behave in a non-case-sensitive way, add this to the top of the sheet module:

Option Explicit
Option Compare Text

Right-click on the ACCESS sheet tab and select VIEW CODE. This will open that sheet module.

Paste the macro I gave you in there. Also add that extra line of code I noted in post #13.

I am not going to do this for you. No offense. But if you can't find and open the sheet module for yourself with the multiple instructions you've been given, you shouldn't be using VBA.

Excellent, you've got the code in the correct module and it IS working. Now, if want it to work the same whether you type "company" or "Company" or "CoMpANy", follow the instruction in post #13 to add that one flag at the top of the module. Then you should be done.

Replace all the code in the sheet module with this:

Option Explicit
Public Prior As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Column = 6 Then Prior = Target.Text

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

For Each cell In Target
    If cell.Column = 6 Then
        Select Case cell.Value
            Case "Company", "Organization", "School", "S. S. C. board"
                Application.EnableEvents = False
                Range("Q" & cell.Row).Resize(, 9).Value = "-"
                Application.EnableEvents = True
            Case Else
                Select Case Prior
                    Case "Company", "Organization", "School", "S. S. C. board"
                        Application.EnableEvents = False
                        Range("Q" & cell.Row).Resize(, 9).Value = ""
                        Application.EnableEvents = True
                    Case Else
                        'do nothing
                End Select
        End Select
    End If
Next cell

End Sub

Good morning JBeaucaire,
Excellent! it's working fine. Now I think to add background music to my contacts file. is it possible through vba? I want to make my contact file more dynamic so I'm modifying it day by day. If you have any suggestions to make it more dynamic and informative I appreciate your suggestions.

I want to keep contact with you regularly as my best friend. I like to make friendship with such a brilliant personalities.

(Moderator's edit - personal contact info removed as per forum rules)

Thanking you in anticipation.

Mukesh

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

New completely unrelated questions should go in a new thread.

-----------------
If you're looking for private consultation, you can reach me through my website, link below in my signature. I believe you can continue to get excellent free assistance, though, right here through the forum.