Reset the value to C in column C if specific text is found "ar"

[hsolanki]

Good Morning everyone

i was wondering if anyone could assist me, someone has kindly written the below code however it doesn't change all the Capital S and lowercase s to C if and when "ar" is entered or found in column C.

Private Sub Worksheet_Calculate()

Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws = wb.Sheets("LogDetails")
Dim Cell As Range

If ws Is Nothing Then
    Exit Sub 'possible way of handing no worksheet was set
End If
If Sheets("LogDetails").Visible = xlSheetVisible Then
Sheets("LogDetails").Visible = xlSheetVeryHidden
Else
Sheets("LogDetails").Visible = xlSheetVisible
End If
  Sheets("LogDetails").Select

Range("C3:C10000").Select
Set Cell = Selection.Find(What:="AR", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

If Cell Is Nothing Then
    MsgBox "Did not found AR"

Else
    Columns("C").Replace What:="S", _
                            Replacement:="C", _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False
    Columns("C").Replace What:="s", _
                            Replacement:="C", _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False
End If

End Sub

 

[Joe4]

Are you getting the message box that it "Did not found AR"?

What does this formula return?

=LEN(C30)

 

[hsolanki]

Hi Joe thank you for your message, yes it does show a message box when not "ar" is found.

=LEN(C30) nothing happens

 

[hsolanki]

Hi sorry when i tried

cell

with the msgbox it returns the value with ar

 

[Joe4]

So, I think you misunderstood.
In your sample, "ar" is in cell C30.
So I just want you to place that formula in any blank cell on your sheet, and tell me what the formula returns:

=LEN(C30)

We are just looking to see if there are any extract characters in that cell.

 

[hsolanki]

Hi i tried pasting

=LEN(C30)

and it comes up with number 2 cell when pasted in a blank cell

 

[Joe4]

So, you are saying that it doesn't work on the sample data you posted the image of?
That when you run the VBA code, you get the message "Did not found AR"?
Is that correct?

Are you sure this data is on the "LogDetails" sheet?
What is the name of the VBA module where this code resides in?
What changes are you making to try and trigger the code to run?

 

[hsolanki]

Morning Joe, No when i run the code, it does finds an AR however when it does found, nothing happens with the code afterwards.

i mean it does not change the S to C

 

[Fluff]

Is col C hard coded, or is it a formula?

 

[hsolanki]

Hi Fluff it is coded so therefore any changes that happens on other tabs it records on to the Sample log (Column C) of all the changes however it would only be initial letters for example S, C, AR, M, H and A